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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.