SAS-Importing and Exporting from Tabs of Excel

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.

1) PROC EXPORT DATA = EXPORT1

            OUTFILE = "C:\Documents and Settings\analyst\Desktop\Exported_From_SAS.xls"

            DBMS = EXCEL REPLACE;

            SHEET = "EXPORT1";

RUN;

2) PROC EXPORT DATA = EXPORT2

            OUTFILE = "C:\Documents and Settings\analyst\Desktop\Exported_From_SAS.xls"

            DBMS = EXCEL REPLACE;

            SHEET = "EXPORT2";

RUN;

3) 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;


URL Referred: http://www.analytics-tools.com/search/label/SAS-_N_