Wednesday, June 13, 2012

SAS Macro to Import all Worksheets of an XLSX file

/* SAS Macro to import all worksheets of an XLSX file */

/* Extra required software includes SAS/ACCESS, SAS/MACRO */
/* Assumes first row contains field names */
/* Assumes worksheets have valid SAS dataset names */ 
/* This macro provided as-is, use at your own risk */

/* Macro parameters:
    libin = libname to be temporarily assigned to XSLX file
    libinpath = path to XLSX file
    libout = libname where SAS datasets should be exported
*/

%macro XLSXimport(libin, libinpath, libout);
    /* Open link to xlsx file */
    libname &libin. "&libinpath";

    proc sql noprint;
        /* Create array with name of each dataset */
        select memname into :mem1 - :mem&sysmaxlong
        from dictionary.tables
        where libname=upcase("&libin"
        and memtype = upcase('data');

        /* Loop to import each dataset to libout */
        %do i=1 %to &sqlobs;
            create table &libout..%substr(&&mem&i,1,
                %EVAL(%LENGTH(&&mem&i)-1)) as
            select * from &libin.."&&mem&i"n ;
        %end;
    quit;

    /* Close link to xlsx file */
    libname &libin. CLEAR;
%mend XLSXimport;

%XLSXimport(mylib, c:\users\public\EXCEL FILE.xlsx, work);
run;

Two Methods to Create a CSV: Proc Export and the Data Step

Sample PROC Export procedure to export a SAS dataset to CSV:

proc export data=work.DATASET_NAME;
     outfile='c:\users\public\EXPORTED_FILE_NAME.csv'
     replace
     dbms=dlm;
     delimiter=',';
run;

Sample DATA Step to export a SAS dataset to CSV:

data _null_;
     set work.DATASET_NAME;
     file 'c:\users\public\EXPORTED_FILE_NAME.csv' dlm=',';
     put var1 var2 var3;
run;


The above two statements are essentially equivalent, with two exceptions: the data step does not output field names to the CSV file, and the data step requires that desired variables be explicitly included in the PUT statement.