I have this code which creates a bunch of tables and then using a macro exports them to an excel file. All the datasets are created to the WORK library and then exported. I want to know how I can edit my code to only export the datasets that have observations in them. Here is the macro I am using to export to excel:
%macro create_ut_xlsx; %macro _; %mend _;
proc export
data = WORK.UT_LIST /* UT_LIST is a table that shows all the other tables and how many observations they have */
dbms = xlsx
outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
replace;
sheet='UT_LIST';
run;
%do n = 1 %to &UT_COUNT;
%let CURR_UT_ID = %scan(%ut_list , &n, |);
%put &CURR_UT_ID.;
proc export
data = WORK.&CURR_UT_ID.
dbms = xlsx
outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
replace;
sheet="&CURR_UT_ID.";
run;
data _NULL_;
if 0 then set WORK.&CURR_UT_ID. nobs = N;
call symputx('CURR_UT_ID_NR_OF_ERRS', N);
stop;
run;
%put In &CURR_UT_ID. the number of issues is &CURR_UT_ID_NR_OF_ERRS.;
data _NULL_;
if 0 then set WORK.&CURR_UT_ID. nobs = N;
proc sql noprint;
update UT_LIST set NR_ERR = &CURR_UT_ID_NR_OF_ERRS.
where UT_ID = "&CURR_UT_ID.";
quit;
%end;
proc export
data = WORK.UT_LIST
dbms = xlsx
outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
replace;
sheet='UT_LIST';
run;
%mend create_ut_xlsx;
%create_ut_xlsx;
Please let me know how I can accomplish this and if you require more info.
Your comments says you have dataset with the list of datasets and number of observations, but you don't say what variables have that information. Let's assume they are called DSNAME and NOBS.
proc export
data = WORK.UT_LIST
dbms = xlsx
outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
replace
;
sheet='UT_LIST';
run;
data _null_;
set WORK.UT_LIST ;
where nobs > 0 ;
call execute(catx(' '
,'proc export data=',dsname,'dbms = xlsx'
,'outfile=',quote("/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx")
,'replace;'
,'sheet=',quote(trim(dsname))
,';run;'
));
run;
Notice how no macro code is needed at all.
Your comments says you have dataset with the list of datasets and number of observations, but you don't say what variables have that information. Let's assume they are called DSNAME and NOBS.
proc export
data = WORK.UT_LIST
dbms = xlsx
outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
replace
;
sheet='UT_LIST';
run;
data _null_;
set WORK.UT_LIST ;
where nobs > 0 ;
call execute(catx(' '
,'proc export data=',dsname,'dbms = xlsx'
,'outfile=',quote("/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx")
,'replace;'
,'sheet=',quote(trim(dsname))
,';run;'
));
run;
Notice how no macro code is needed at all.
Sorry Im a bit new to SAS programming so Im not sure what you mean by "what variables have that information". I'll try to further explain my program and what it does hopefully that helps. Basically I have a proc sql statement which creates tables 1-78 called UT_01, UT_02, etc. These tables are created to the WORK library along with UT_LIST which has the UT_ID (Table names), NR_ERR (number of observations), UT_TEXT (Descriptor). When NR_ERR is 0 the dataset is empty, and so that UT_ID table should not be exported. Hope that helps.
The code as posted by @Tom does what you request. Since you did not provide any details he used NOBS instead of NR_ERR and Dsname instead of UT_ID which are variable names in your UT_LIST data set from your description.
Hint: for future questions start with a description of your data set. Better is an actual example data and best is for the example to be in the form of data step code so we have some to test.
Yes, I was able to get it working with @Tom code so thank you for that Tom.
Thanks for the feedback will remember that for future posts.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: