topic Re: choose all file name for macro variable by using loop in SAS Programming

choose all file name for macro variable by using loop

dht115 — Thu, 27 Apr 2023 20:48:28 GMT

Hello,

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.

For scenario 1, I do have five (5) sas files in folder so I need to run my macro five times.
Whereas, in scenario 2, I do have three (3) sas files in folder - and I do need to run my macro only three times.

Scenario 1:

Scenario 2:

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);

Re: choose all file name for macro variable by using loop

A_Kh — Thu, 27 Apr 2023 21:09:03 GMT

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.

Eg:

%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);

Re: choose all file name for macro variable by using loop

dht115 — Mon, 01 May 2023 11:55:02 GMT

Getting an error:

ERROR: File WORK.LIST.DATA does not exist.

Re: choose all file name for macro variable by using loop

PaigeMiller — Mon, 01 May 2023 11:59:25 GMT

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.

options mprint;

Re: choose all file name for macro variable by using loop

Quentin — Mon, 01 May 2023 12:52:52 GMT

@dht115 wrote:

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.

Re: choose all file name for macro variable by using loop

yabwon — Mon, 01 May 2023 12:34:20 GMT

Try like this:

Test data:

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.

Bart

Re: choose all file name for macro variable by using loop

A_Kh — Mon, 01 May 2023 13:36:41 GMT

Log says: "WORK.LIST data does not exist". Macro will read data from ABC library. You need to declare LIBNAME first, then use this lib as macro parameter.

Libname abc "C/myfolder/myfiles";