ABSTRACT INTRODUCTION FILE IMPORT WIZARD

(1)

SAS

®

_{System Generates Code for You while Using Import/Export Procedure}

Anjan Matlapudi and J. Daniel Knapp

Pharmacy Informatics,

PerformRx, The Next Generation PBM,

200 Stevens Drive,

Philadelphia, PA 19113

ABSTRACT

It is a very common programming task to input data from flat files and export data into flat files.

Most of these files are delimited text (tab, pipe, comma, etc.) or CSV files come from various

sources such as third party vendor software. Earlier we demonstrated techniques to read

complex flat files extracted from DB2 data source.

Many of us use different approaches to read and write data files such as text, ASCII, sequential

or flat files. Some of us might not be aware of how the SAS

®

_{system generates code. This}

paper illustrates an easy way to obtain the actual SAS code while importing or exporting data

from an external source using either the IMPORT/EXPORT wizard or IMPORT/EXPORT

procedures.

INTRODUCTION

The SAS® system has an excellent facility for importing and exporting data. We import flat files in the SAS system to perform further analysis and output results based on our business requirements. In this paper we will demonstrate an easy method to obtain SAS system generated code using the SAS IMPORT/EXPORT wizard and IMPORT/EXPORT procedures.

FILE IMPORT WIZARD

We now demonstrate the steps to obtain SAS generated code from the IMPORT wizard,

specifically importing a tab delimited text file. We hope the following steps and screen shots will

help you to import and obtain the SAS system generated code.

(2)

Step 2: You have an option to choose file type; in this example highlight tab delimited file.

Step 3: Locate the file by browsing the directory path and highlight file name to select.

Step 4: Input a name for the data set ( by default the imported dataset will reside in the SAS work

(3)

***Step 5: This is most important step to remember. Open a new program window or existing

program window and press the F4 key (recall key), and magically the code will appear. You

have the option now to make changes to the logic.

FILE EXPORT WIZARD

This same methodology applies to the EXPORT wizard.

FILE IMPORT PROCEDURE

You can also import any kind of delimited flat files using the file IMPORT procedure and get the

SAS system generated code as shown below.

*---Import Procedure to Import Comma Delimited Flat File---*;

proc import datafile='C:\Anjan Personnel\NESUG 2012\Import Export\Comma Delimited Text file.txt'

out=DataOut dbms=dlm; delimiter=","; run; /********************************************************************** * PRODUCT: SAS * VERSION: 9.2

* CREATOR: External File Interface * DATE: 10SEP12

* DESC: Generated SAS Datastep Code * TEMPLATE SOURCE: (None Specified.)

********************************************************************** data WORK.DATAOUT ;

%let _EFIERR_ = 0;

infile 'C:\NESUG 2012\Import Export\Comma Delimited Text file.txt' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

informat Row best32. ; informat Member_Number $4. ; informat Claim_Number best32. ; informat Date_Filled mmddyy10. ; informat Ammount_Paid comma32. ; format Row best12. ;

(4)

format Claim_Number best12. ; format Date_Filled mmddyy10. ; format Ammount_Paid comma12. ; input Row Member_Number $ Claim_Number Date_Filled Ammount_Paid;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

run;

You can also import CSV files as shown and obtain the system generated code by pressing F4

key.

*---CSV File---*;

proc import datafile='C:\Anjan Personel\NESUG 2012\Import Export\CSV_Formated.csv'

out =CVSOut; run;

FILE EXPORT PROCEDURE

Similarly, you can also work with PROC EXPORT using the file export wizard or running the

following code to get the system generated code.

PROC EXPORT DATA=connmadel

OUTFILE='C:\NESUG 2012\Import Export\CommaDelimited.txt' DBMS=DLM replace ;

DELIMITER=','; RUN;

By pressing F4 key on a new program window this following code will be generated.

data _null_; %let _EFIERR_ = 0;

file 'C:\NESUG 2012\Import Export\CommaDelimited.txt' delimiter=',' DSD DROPOVER lrecl=32767;

if _n_ = 1 then do; put "Row" ',' "Member_Number" ',' "Claim_Number" ',' "Date_Filled" ',' "Ammount_Paid" ; end;

set CONNMADEL end=EFIEOD; format Row best12. ; format Member_Number $4. ; format Claim_Number best12. ; format Date_Filled mmddyy10. ; format Ammount_Paid comma12. ; do;

EFIOUT + 1; put Row @;

(5)

put Claim_Number @; put Date_Filled @; put Ammount_Paid ; ;

end;

if _ERROR_ then call symputx('_EFIERR_',1); if EFIEOD then call symputx('_EFIREC_',EFIOUT); run;

You can cleanup this as shown below to output delimited text file.

data _null_;

file 'C:\Anjan Personel\NESUG 2012\Import Export\CommaDelimited.txt' delimiter=',' DSD DROPOVER lrecl=32767;

set CONNMADEL(drop=row) ; format Member_Number $4. ; format Claim_Number best12. ; format Date_Filled mmddyy10. ; format Ammount_Paid comma12. ; put Member_Number $ @;

put Claim_Number @; put Date_Filled @; put Ammount_Paid ;

if _ERROR_ then call symputx('_EFIERR_',1); run;

IMPORT/EXPORT THROUGH MACRO VARIABLES

Once you change the system generated code as per the logic, you can use positional macros to

work with many files as shown below.

*--Files import using Macro Variable---*; %FileImport (InFileName = ,OutFileName=);

proc import datafile="C:\NESUG 2012\Import Export\In &In_FileName..txt" out=&OutFileName.

dbms=dlm; delimiter=","; run;

%mend;

%FileExport (InFileName =FileIn1 ,OutFileName=FileOut1); %FileExport (InFileName =FileIn2 ,OutFileName=FileOut2);

The following logic is used to export many files using macro variables.

*--Files Export using Macro Variable---*; %macro FileExport(FileIn=, FileOut=); data _null_;

%let _EFIERR_ = 0;

file "C:\NESUG 2012\Import Export\&FileOut..txt" delimiter=',' DSD DROPOVER lrecl=32767;

set &FileIn. (drop=row) ; format Member_Number $4. ; format Claim_Number best12. ; format Date_Filled mmddyy10. ; format Ammount_Paid comma12. ; put Member_Number $ @;

put Claim_Number @; put Date_Filled @; put Ammount_Paid ; ;

(6)

run; %mend;

*--Passing FileName as Psotional Marco Vriable---*; %FileExport (FileIn=commadel, FileOut=CommaDelout1) %FileExport (FileIn=commadel, FileOut=CommaDelout1)

CONCLUSION

We hope this paper will be useful to educate SAS users to use the SAS IMPORT/EXPORT

wizards and IMPORT/EXPORT Procedures, in conjuction with the F4 short cut key to obtain SAS

generated code. You can modify the code based on your needs and preferences. We also

demonstrated use of macro variables to handle multiple files and datasets.

REFERENCES

Anjan Matlapudi and J. Daniel Knapp ‘Challenge! Reading Mainframe Hex Delimited Flat File

Where Each Line Has Different Layout.

Global SAS Forum 2010

Coders’ Corner, Paper

107-2010

Kuligowski, T. Andew. Datalines, Sequential Files, CVS, HTML and More – Using INFILE and

INPUT Statements to Introduce External Data into the SAS System,

SUGI 31

Tutorials Paper

228-31.

AKNOWLEDGMENTS

We would like to acknowledge Mr. Shimels Afework, Senior Director, PerformRx.

PerformRx provides pharmacy benefit management (PBM) services through proactively

managing escalating pharmacy costs while focusing on clinical improvement and financial results.

CONTACT INFORMATION: