/* SCENARIO */
/* HAVE A RAW TXT FILE (CSV) WITH MASTER DETAIL DATA UNIQUE BY ACCT_NO AND A LOOKUP BRANCH TABLE IN SAS */
LIBNAME VALSUG '/home/*****/valsug';
/* CREATE DATASETS TO CREATE SCENARIO*/
DATA VALSUG.MASTER_DATA_CHK;
  INPUT ACCT_NO BRANCH_NO $ PRODUCT $;
  CARDS;
111222 1000A CC
111333 1000B CC
111555 1000C HE
111666 1000D CC
111777 1000B HE
111888 1000E CC
111999 1000A HE
;
RUN;

/* CREATE DETAIL DATA SET IN RAW TEXT FILE (CSV)*/
DATA _NULL_;
  SET VALSUG.MASTER_DATA_CHK;
  FILE '/home/*****/sample_master_data.csv' delimiter = ',' DSD dropover LRECL=32767;
    FORMAT ACCT_NO BEST12.;
    FORMAT BRANCH_NO $8.;
    FORMAT PRODUCT $8.;
 /* IF _N_ EQ 1 THEN
   DO;
     PUT 'ACCT_NO' ',' 'BRANCH_NO' ',' 'PRODUCT';
   END;
   ELSE
    DO; */
     PUT ACCT_NO @; PUT BRANCH_NO $ @; PUT PRODUCT $;
    *END;
RUN;

/* CREATE LOOKUP BRANCH TABLES IN A SAS DATASET */
DATA VALSUG.OLD_BRANCH_TABLE;
  INPUT BRANCH_NO $ BRANCH_NAME $ REGION $;
  CARDS;
1000A GREEN MIDWEST
1000B EASTON WEST
1000C MIDVILLE WEST
1000D COLBY EAST
1000E SUDDER EAST
1000F ALBANY EAST
;
RUN;


/* TASK: */
/* NEED TO IDENTIFY ALL OF THE ACCOUNTS IN THE EAST BRANCHES AND THE BRANCH NAME ASSOCIATED WTH EACH ACCT */

/* USING MERGE STATEMENT */
/* INORDER TO USE MERGE STATEMENT - MUST PULL RAW CSV TEXT FILE INTO A SAS DATASET */
/*---------------------------------------------------------------------------------*/
DATA MASTER_DATA;
  INFILE '/home/*****/sample_master_data.csv' delimiter = ',';
  INPUT  @1 ACCT_NO 6. @8 BRANCH_NO $ @14 PRODUCT $2.;
RUN;

/* USING MERGE STATEMENT */
PROC SORT DATA=MASTER_DATA;
  BY BRANCH_NO;
RUN;

PROC SORT DATA=VALSUG.OLD_BRANCH_TABLE;
 BY BRANCH_NO;
RUN;

DATA MASTER_DATA_EAST;
  MERGE MASTER_DATA (IN=INA) VALSUG.OLD_BRANCH_TABLE (IN=INB);
  BY BRANCH_NO;
  IF INA;
  IF REGION IN ('EAST') THEN OUTPUT;
RUN;

TITLE 'Using MERGE Statement';
PROC PRINT;
RUN;
/*---------------------------------------------------------------------------------*/



/* USING PROC SQL LEFT JOIN */
/* INORDER TO USE PROC SQL LEFT JOIN - MUST PULL RAW CSV TEXT FILE INTO A SAS DATASET */
/*---------------------------------------------------------------------------------*/
DATA MASTER_DATA;
  INFILE '/home/*****/sample_master_data.csv' delimiter = ',';
  INPUT  @1 ACCT_NO 6. @8 BRANCH_NO $ @14 PRODUCT $2.;
RUN;

PROC SQL;
  CREATE TABLE MASTER_DATA_EAST2 AS
  SELECT A.*, B.BRANCH_NAME
  FROM MASTER_DATA A LEFT JOIN VALSUG.OLD_BRANCH_TABLE B
  ON A.BRANCH_NO = B.BRANCH_NO
  WHERE REGION EQ 'EAST';
QUIT;

TITLE 'Using PROC SQL Left Join';
PROC PRINT;
RUN;
/*---------------------------------------------------------------------------------*/



/* USING PUT STATEMENT */
/*---------------------------------------------------------------------------------*/
DATA MASTER_DATA;
  INFILE '/home/*****/sample_master_data.csv' delimiter = ',';
  INPUT  @1 ACCT_NO 6. @8 BRANCH_NO $ @14 PRODUCT $2.;
RUN;

DATA _NULL_;
  FILE '/home/*****/Build_Branch_Info.sas';
  SET VALSUG.OLD_BRANCH_TABLE;
  PUT "IF BRANCH_NO EQ '" BRANCH_NO + (-1) "' THEN DO;";
    PUT "BRANCH_NAME = '" BRANCH_NAME + (-1) "'; ";
    PUT "REGION = '" REGION + (-1) "'; END; ";
 RUN;

DATA MASTER_DATA_EAST3;
  SET MASTER_DATA;

   LENGTH REGION $8. BRANCH_NAME $8.;
   %inc '/home/*****/Build_Branch_Info.sas';

   IF REGION EQ 'EAST' THEN OUTPUT;
RUN;

TITLE 'Using PUT Statement ';
PROC PRINT;
RUN;
/*---------------------------------------------------------------------------------*/



/* USING PROC FORMAT AND PUT STATEMENT */
/*---------------------------------------------------------------------------------*/

DATA FORMAT(KEEP = START LABEL FMTNAME);
  SET VALSUG.OLD_BRANCH_TABLE;
   WHERE REGION EQ 'EAST';
   START = BRANCH_NO;
   LABEL = 'EAST';
   FMTNAME = '$EBRANCH';
RUN;

DATA _NULL_;
  FILE '/home/*****/Build_Branch_Name.sas';
  SET VALSUG.OLD_BRANCH_TABLE;
  PUT "IF BRANCH_NO EQ '" BRANCH_NO + (-1) "' THEN BRANCH_NAME = '" BRANCH_NAME + (-1) "';";
RUN;

PROC FORMAT CNTLIN=FORMAT;
RUN;

DATA MASTER_DATA_EAST4;
  INFILE '/home/*****/sample_master_data.csv' delimiter = ',';
  INPUT @1 ACCT_NO @8 BRANCH_NO $ @;
   IF PUT(BRANCH_NO, $EBRANCH.) = 'EAST' THEN DO;
     INPUT @14 PRODUCT $2.;
   END;
  LENGTH BRANCH_NAME $8.;
  %inc '/home/*****/Build_Branch_Name.sas';

RUN;

TITLE 'Using PROC FORMAT ';
PROC PRINT;
RUN;
/*---------------------------------------------------------------------------------*/



/* USING DATA MODIFY */
/*---------------------------------------------------------------------------------*/

DATA MASTER_DATA5 ;
  INFILE '/home/*****/sample_master_data.csv' delimiter = ',';
  INPUT  @1 ACCT_NO 6. @8 BRANCH_NO $ @14 PRODUCT $2.;

  LENGTH BRANCH_NAME $8. REGION $8.;
  BRANCH_NAME = ' ';
  REGION = ' ';
RUN;

PROC DATASETS LIBRARY=WORK NOLIST;
  MODIFY MASTER_DATA5;
  INDEX CREATE BRANCH_NO;
RUN;

PROC SORT DATA=VALSUG.OLD_BRANCH_TABLE OUT=VALSUG.OLD_BRANCH_TABLE
            (RENAME=(BRANCH_NAME=BRANCH_NAMEX REGION = REGIONX));
  BY BRANCH_NO;
RUN;

/* _IORC_ = 0 indicates that the MODIFY statement was successful,
   and that a match was found in the transaction data set. */
/* Override the _ERROR_ flag to prevent SAS from dumping out any records
   in the transaction data set that are not in the master */
/* Use the autocall macro %SYSRC with _IORC_ to monitor sucess of MODIFY */
/* _dsenom indicates no matching observation.  Used with the KEY= */

DATA MASTER_DATA5;
  SET VALSUG.OLD_BRANCH_TABLE;
  DO UNTIL (_IORC_ = %SYSRC(_dsenom));
    MODIFY MASTER_DATA5 KEY=BRANCH_NO;
    IF _IORC_ = 0 THEN DO;
      BRANCH_NAME = BRANCH_NAMEX;
      REGION = REGIONX;
      IF REGION = 'EAST' THEN REPLACE;
       ELSE REMOVE;
    END;
    ELSE DO;
      _ERROR_=0;
    END;
  END;
RUN;

TITLE 'Using DATA MODIFY ';
PROC PRINT DATA=MASTER_DATA5;
RUN;
/*---------------------------------------------------------------------------------*/



/* Resulting data sets were all very similar, and accomplished our task.  Exactly which scenario you should
   use really depends on the data you are working with and your available resources.
   Ask yourself the following?
     Are my data sets indexed?
     Do I have a space or processing issue with rebuilding my entire master data set?
     Do I need to beware of CPU issues?

  The purpose of this presentation is just to let you know of several options available for you to play
  with and decide if any of these options will work better in your environment with your data. */