topic Re: Applying a macro to all datasets in a library. in SAS Programming

Applying a macro to all datasets in a library.

mandonium — Tue, 01 Mar 2022 04:48:54 GMT

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.

Re: Applying a macro to all datasets in a library.

andreas_lds — Tue, 01 Mar 2022 05:52:20 GMT

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.

Re: Applying a macro to all datasets in a library.

Kurt_Bremser — Tue, 01 Mar 2022 09:13:42 GMT

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;

Re: Applying a macro to all datasets in a library.

mandonium — Tue, 01 Mar 2022 09:28:03 GMT

Sorry, datasets should read
ds001.sas7bdat
ds002.sas7bdat
ds003.sas7bdat