SAS-Importing and Exporting from Tabs of Excel

TO AND FRO FROM TABS OF AN EXCEL SHEET
 
Importing/Exporting from/into Tabs of an Excel sheet can be very useful. There can be many situations where this becomes absolutely essential, think of a case when you have a SAS dataset which has more than 65,536 rows (limit of Excel 2003) and all you want to do is export the entire SAS dataset into one excel workbook then this becomes very useful.
Let us discuss the case described above, first we divide the Master Dataset into Datasets with each having Rows< 65,536

DATA EXPORT1 EXPORT2 EXPORT3;
SET SAMPLE;
IF _n_ <= 65,536 THEN OUTPUT EXPORT1;
ELSE IF 65,536< _n_ <= 100,000 THEN OUTPUT EXPORT2;
ELSE OUTPUT EXPORT3;
RUN;
Now that we have three datasets we export it into three tabs of an excel workbook.

PROC EXPORT DATA = EXPORT1
            OUTFILE = "C:\Documents and Settings\analyst\Desktop\Exported_From_SAS.xls"
            DBMS = EXCEL REPLACE;
            SHEET = "EXPORT1";
RUN;
PROC EXPORT DATA = EXPORT2
            OUTFILE = "C:\Documents and Settings\analyst\Desktop\Exported_From_SAS.xls"
            DBMS = EXCEL REPLACE;
            SHEET = "EXPORT2";
RUN;
PROC EXPORT DATA = EXPORT3
            OUTFILE = "C:\Documents and Settings\analyst\Desktop\Exported_From_SAS.xls"
            DBMS = EXCEL REPLACE;
            SHEET = "EXPORT3";
RUN;
This would Export the datasets into the tabs of the Excel workbook, and of course you can increase or decrease the number of Tabs or Datasets, or have a row distribution as per your choice.
 
IMPORTING FROM EXCEL TABS:
Next we import from the tabs of an Excel workbook. Here I have used a macro; I could have used similar looking multiple codes like above but then we also need to optimize stuff.
So the macro imports the tabs one by one and then appends it to one data set. The Proc Delete Statement is because if you run the step twice for some reason the new created datasets will get appended to the dataset created in the previous run. There is this one small issue with proc append, the dataset does not get replaced but it gets appended. So the Macro imports 5 tabs of the Excel workbook (you can change this)
 
%MACRO IMPORT_EXCEL();
%DO i=1 %TO 5 ;
PROC DELETE DATA =OUT.FINALSET;
RUN;
PROC IMPORT OUT = OUT.IMPORT_&i.
DATAFILE= "C:\Documents and Settings\analyst\Desktop\Imported_Into_SAS.xls"
DBMS=EXCEL REPLACE;
SHEET="SHEET &i.$";
GETNAMES=YES; MIXED=NO; SCANTEXT=YES;USEDATE=YES; SCANTIME=YES;
RUN;
PROC APPEND BASE = OUT.IMPORTED_&QTR. DATA = OUT.&QTR._&&I. FORCE;
RUN;
%END;
%MEND;
     

  1. gravatar

    # by lee - September 22, 2009 at 9:08 PM

    Could you please also write a macro for exporting

    Regards
    Lee

Blog Widget by LinkWithin

Search this blog..

Loading