I am running a SAS macro to sort my dataset. I do have multiple folders and each folder has different number of datasets. Is there a way for us to modify SAS macro which can go to specific folder and bring all sas file name as macro parameter. So I just need to change library name and run the macro instead of changing sas dataset name for macro variable.
libname abc 'C:\Users\XXX\Desktop\platform\one'; %macro test (var); proc sort data=&var. out=new_sort_&var.; by item; run; %mend; %test(test1); %test(test2); %test(test3); %test(test4); %test(test5);
Use DICTIONARY.TABLES to import all dataset names from the directory, then load them into a macro variable. In the macro definition use loop to sort all datasets dynamically without physically referring to a dataset name.
%macro sort(dir=); proc sql noprint; select distinct memname into: list separated by ' ' from dictionary.tables where libname eq "%upcase(&dir)"; %put &=list; quit; %do i=1 %to %sysfunc(countw(&list)); %let var= %scan(&list, &i); proc sort data=&var. out=new_sort_&var.; by item; run; %end; %mend; %sort(dir= abc);
Whenever you get an error in the log, show us the ENTIRE log for this code. Do not show us partial logs.
Since this involves macros, please turn on the macro debugging option by running the code below and then run your macro again and show us the ENTIRE log. Do not show us partial logs.
Getting an error:
ERROR: File WORK.LIST.DATA does not exist.
The most likely way that could happen would be if you forgot an & in:
%let var= %scan(&list, &i); %* <-- check there is an & in front of list ;
Another possibility is that you didn't create the libref ABC prior to running the macro. In that case you will see in the log that the select statement returns 0 obs and it does not create the macro variable LIST. So you would have unresolved macro variable reference message in the log as well.
Try like this:
options dlcreatedir; libname t "R:\test1"; data t.a t.b t.c t.d t.e; set sashelp.class; run; libname t "R:\test2"; data t.a t.b t.c t.d t.e; set sashelp.class; run; libname t "R:\test3"; data t.a t.b t.c t.d t.e; set sashelp.class; run;
Sorting for multiple directories:
%macro sort(lib,by); libname t "&lib."; proc contents data=t._ALL_ out=list noprint; run; proc sort data=list(keep=libname memname) nodupkeys; by libname memname; run; data _null_; set list; call execute(cats("proc sort data=",libname,".",memname,"; by &by.; run;")); run; libname t clear; %mend sort; data _null_; input; call execute('%sort(' !! _INFILE_ !! ',age)'); /* <----------- you can modify sorting variables here */ cards4; R:\test1 R:\test2 R:\test3 ;;;; run;
different directories are fed through the CARDS4 to a data step which call macro execution for datasets in that directories.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.