PROC EXPORT - HANDY CODE TO EXPORT TO MULTIPLE SHEETS

[Aurelio]

Hi All,

Not Sure if this is the correct forum to place this on.

Since I have found this forum very useful I thought I'd add some notes for others that may benefit of some simple to use code.

regards Aurelio

* ----------------------------------------------------------------;

* AN EXAMPLE OF USING PROC EXPORT TO EASILY BUILD AN EXCEL REPORT ;

* WITH MULTIPLE SHEETS MATCHING THE TABLES YOU WANT TO STORE      ;

*                                                                 ;

* SOMETIMES IT IS EASIER TO VISUALISE AND VERIFY SAMPLE TEST DATA ;

* IN EXCEL                                                        ;

* PROC EXPORT IS A VERY HANDY AND SIMPLE TO USE PROC              ;

* ----------------------------------------------------------------;

　

* BUILD YOUR SAMPLE DATA - PART1;

data REP1_DATA;

set SASHELP.SHOES;

run;

data REP2_DATA;

set SASHELP.cars;

run;

data REP3_DATA;

set SASHELP.bweight;

run;

* BUILD YOUR SAMPLE DATA PART 2                    ;

*   THIS IS ANOTHER METHOD TO BUILD A SUMMARY TABLE;

proc sql;

create table TABLE_SUMMARY as

select 'REP1_DATA' as TABLE_NAME, nobs as Num_of_Obs

from sashelp.vtable where libname='WORK' and memname='REP1_DATA'

union

select 'REP2_DATA' as TABLE_NAME, nobs as Num_of_Obs

from sashelp.vtable where libname='WORK' and memname='REP2_DATA'

union

select 'REP3_DATA' as TABLE_NAME, nobs as Num_of_Obs

from sashelp.vtable where libname='WORK' and memname='REP3_DATA'

order by 1;

quit;

* NOW OUTPUT ALL THE SAMPEL TABLES INTO EXCEL;

proc setinit; run;

%macro expToXLfile(table);

proc export

data=&table label

outfile=XLFILE replace

dbms=xlsx;

sheet="&table";

run;

%mend expToXLfile;

* SET THE OUTPUT EXCEL FILE ... change to suit OS;

* THE EXCEL REPORT

filename XLFILE "/TESTING/SAMPLE_TestResults.xlsx";

%expToXLfile(TABLE=REP1_DATA);

%expToXLfile(TABLE=REP2_DATA);

%expToXLfile(TABLE=REP3_DATA);

%expToXLfile(TABLE=TABLE_SUMMARY);

Hope someone finds this useful.

Have a great day!

[RW9]

Well, whilst not using Excel in the first place would be the ideal scenario, you can just dump datasets directly to a file using libname excel - no need for macros and such like:

libname mylib excel "c:\test.xlsx";
data mylib.a;
  set sashelp.class;
run;
libname mylib clear;

However if you beutified output - i.e. anything other than very basic, your better off looking at one of the various tagsets = excelxp, excel for instance.  With those you can control all aspects of the export.

[Aurelio]

Thanks for comments.

The handy thing I found was to write to any sheet and the replace option
permitted updates without replacing the entire workbook.

Ill look into making pretty reports later.. thanks for the hints.

##- Please type your reply above this line. Simple formatting, no
attachments. -##