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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.