Hello,
I'm trying to apply a macro to all datasets in a library without have to list the names of all datasets. The output should be a .xlsx file where the tabs are labelled according to the names of the datasets.
"C:\data\" contain the following datasets:
ds001.sas
ds002.sas
ds003.sas
Here is my code:
%let study=903001;
libname f&study. "C:\data\" inencoding=any;
%let rundate = %SYSFUNC(today(),yymmddd10.);
goptions device=actximg;
ods _all_ close;
ods excel file="C:\output\&study._listing_&rundate..xlsx"
style=excel;
%macro safelist(dsin);
ods excel options
(flow='tables'
autofilter='on'
frozen_headers='on'
sheet_name= "&dsin");
proc report data=f&study..&dsin.
split='\'
style(column)={fontfamily='Malgun Gothic Semilight'
fontsize=1 vjust=t}
style(header)={fontfamily='Malgun Gothic Semilight'
fontsize=1
just=l
vjust=t
background=#00FFFF};
run;
%mend;
%safelist (dsin=f&study.);
The error message in the log is this:
1090 %safelist (dsin=f&study.);
ERROR: File F903001.F903001.DATA does not exist.
If I use:
%safelist (ds001);
%safelist (ds002);
%safelist (ds003);
it works. I'm trying to avoid having to list all datasets in a library.
Thank you.
Hello,
I'm trying to apply a macro to all datasets in a library without have to list the names of all datasets. The output should be a .xlsx file where the tabs are labelled according to the names of the datasets.
"C:\data\" contain the following datasets:
ds001.sas
ds002.sas
ds003.sas
Something is really wrong here. Datasets have the extensions sas7bdat, files ending with "sas" are files containing sas-code, accessing those files by using a libname statement is not possible.
Your macro expects a single level dataset name (without library) as parameter, so you cannot use a library reference there.
You need to call your macro for every dataset within your source library, and you can do that from SASHELP.VTABLE:
data _null_;
set sashelp.vtable;
where libname = upcase("f&study.");
call execute('%nrstr(%safelist(' !! strip(memname) !! '))');
run;
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.