SAS in Mainframes(z/Os) Tutorial with xamples - Part 2 ( Creating csv/excel file from mainframe dataset using SAS)

1. We will see how we can Merge two or more  input files in SAS and routing it to one output dataset and USE the same dataset to prepare a report in excel format
Creating .xls file on Z/os
(Refer to  Previous  posts to know basic steps in sas)
Lets take two input file, RXX.TEST.FILE3 and RXX.TEST.FILE4 with the fields
CITY,DATE,STATE,AMT in FILE3 and CITY,DATE,STATE,BANK in FILE4.We want to merge both the files so that the output contains CITY DATE STATE BANK.For achieving this, we need have atleast one common field in both the files based on which we can join these two datasets. We will be joining based on CITY.So the steps should be as follows.
Step1.Create the SAS dataset from input file 3 and SORT it on the key field
Step2.Create the SAS dataset from input file 4 and SORT it on the key field
Step3.create a new SAS dataset Using  the MERGE keyword in SAS along with the key field and finally
Step4. take the fields which we need

//SAS01     EXEC SAS                                
//POLIN    DD DSN=RXX.TEST.FILE3,DISP=SHR
//POLIN2   DD DSN=RXX.TEST.FILE4,DISP=SHR
//OUTFILE   DD DSN=RXX.TEST.FILEOUT,DISP=(,CATLG),
//             SPACE=(TRK,(20,20),RLSE),LRECL=180,RECFM=FB
//WORK      DD SPACE=(CYL,(50,10),RLSE)                   
//SYSIN     DD *   
OPTION NOCENTER;                   
OPTION SORTLIB='';                 
  DATA POLIN;                      
   INFILE POLIN;                   
   INPUT @01 CITY   $CHAR02.       
         @06 DATE   $CHAR08.       
         @14 STATE  $CHAR02.       
         @16 AMT    COMMA9.2;      
   PROC SORT DATA=POLIN NODUPS;    
     BY CITY;                                                          
  DATA POLIN2;                     
   INFILE POLIN2;                  
   INPUT @01 CITY   $CHAR02.       
         @06 DATE   $CHAR08.       
         @14 STATE  $CHAR02.       
         @16 AMT    COMMA9.2       
         @25 BANK   $CHAR5;        
                                   
   PROC SORT DATA=POLIN2 NODUPS;   
     BY CITY;                      
  DATA COMMON;                     
  MERGE POLIN(IN=D1) POLIN2(IN=D2);
  BY CITY;                         
  IF D1 AND D2 THEN OUTPUT;        
  PROC PRINT DATA=COMMON;          
     VAR CITY DATE STATE BANK;     
 RUN;                              
 DATA _NULL_;                           
    SET COMMON;                         
    FILE OUTFILE;                       
    PUT CITY ',' DATE ',' STATE ',' BANK;
RUN;                                    
Here goes the output for the same:
***********************
CA ,20130320 ,WB ,BANK1
CA ,20130120 ,TN ,BANK6
CA ,20130120 ,TN ,BANK6
CA ,20130320 ,KA ,BANK6
MI ,20130120 ,KA ,BANK1
MI ,20130320 ,AP ,BANK1
RR ,20130120 ,AP ,BANK8
************************
Why Do we use DATA _NULL_ in SAS ?  This simply is used when we want to make a report.
_NULL_  is a SAS keyword which does not create any SAS dataset.

2. Creating the excel report / CSV file from the mainframe dataset.
To add column headings in SAS to be used in excel sheet, we can use the DATA _NULL_ statement as well.
FILE OUTFILE  DLM=',';
IF _N_=1 THEN DO;
 PUT     'CITY,' 
         'DATE,'
         'STATE,' 
         'BANK'
;
END;

PUT  CITY 
     DATE
     STATE
     BANK
;
To Create a CSV File from a mainframe Dataset we can use the same above code with The delimiter option. DLM=','.(Imp point to remember. Delimeter is the key in creating .xls file)
 (If you remember we need to use delimited option in excel to prepare a formatted report from notepad. DLM option in SAS takes care of that ).
The line of code _N_=1 has special significance. We will check it later. However you can try running the program without using the specific line and see what happens.:)
So we  can download the dataset from command shell (option 6) in ISPF and use 'Receive from Host' option. Save the File in .csv format.
Or otherwise  put one FTP step (where u want to put the report) after the mainframe DATASET is created and save the file in filename_youwant.csv. No need to create a text file and convert it into excel sheet. The FTP location will contain the .xls file and ready to use!!