How to: loop through SAS tables

[noling]

Perhaps you want to preform a similar operation on multiple tables. Macro-tizing repetitive code can help. I prefer to use a %scan loop to loop through SAS tables or variables. If you can make a list of tables to loop through and get the count of the tables, the %scan function will do the hard work for you! This is most useful when you have a large number of tables to process, or are lazy and don't want to copy and paste code many times. Here are 3 examples going from brute-force to most efficient. The %scan function is in the 3rd example.

Examples for getting record counts with proc sql for 3 tables:

1. Hard-coded:

proc sql noprint;
	select count(*) into: count
	from sashelp.cars;
quit;
%put record count for cars = %sysfunc(strip(&count));
proc sql noprint;
	select count(*) into: count
	from sashelp.class;
quit;
%put record count for class = %sysfunc(strip(&count));
proc sql noprint;
	select count(*) into: count
	from sashelp.baseball;
quit;
%put record count for baseball = %sysfunc(strip(&count));

Log shows counts from %put statements:

record count for cars = 428

record count for class = 19

record count for baseball = 322

2. Single macro calls:

%macro get_record_count(table);
	proc sql noprint;
		select count(*) into: count
		from sashelp.&table;
	quit;
	%put record count for &table = %sysfunc(strip(&count));
%mend;
%get_record_count(table=cars)
%get_record_count(table=class)
%get_record_count(table=baseball)

Same log messages as option #1.

3. Using the %scan loop. The %scan function will use the spaces between words as the default delimiter and return the same results:

%let tables_to_process=cars class baseball;

%macro loopCall_record_count_macro(tables_to_process);
	
	%let to_loop_count = %sysfunc(countw(&tables_to_process));
	%put &to_loop_count;
	%put will process &to_loop_count tables &tables_to_process;

	%do i = 1 %to &to_loop_count;
		%let table = %scan(&tables_to_process,&i);
		proc sql noprint;
			select count(*) into: count
			from sashelp.&table;
		quit;
		%put record count for &table = %sysfunc(strip(&count));
	%end;
	
%mend;
%loopCall_record_count_macro(&tables_to_process)

Order of operations:

1. List of tables to process defined in &tables_to_process with spaces between tables to loop over.

2. Macro is compiled then called

3. &to_loop_count is set to 3

4. %scan pulls out the 1st value (i=1 here), and assigns the value to &table, which is cars

5. proc sql runs against sashelp.cars

6. %scan pulls out the 2nd value (i=2), and assigns the value to &table, which is class, then proc sql runs again

7. %scan pulls out the 3rd value (i=3), and assigns the value to &table, which is baseball, then proc sql runs again

The log is the same for all 3 methods, wow!

To be even more efficient you could take the following steps, or add your own ideas!

1. Build the&tables_to_process using proc sql's "select into"

2. Use %sysfunc(attrn(&dsid, nobs), proc contents with an 'out=' option , or sashelp or  proc sql's dictionaries tables to get the number of records in a dataset.

[ScottBass]

Another approach:

proc sql noprint;
   select catx(".",libname,memname) 
   into :tables 
   separated by " "
   from dictionary.tables
   where libname="SASHELP" 
     and memtype="DATA"
   ;
quit;

%put &=tables;

%macro code;
   %put TABLE: %sysfunc(putc(&word,$20.)) %sysfunc(putn(%nobs(&word),comma15.));
%mend;
%loop(&tables)

You could get NOBS directly from the dictionary table, this is just for illustration. 

For example, you could change the %code macro to proc print data=&word (obs=10);run;

The idea is to abstract the macro looping into its own macro, with the child macro %code written at "run time" and called for every token in the list passed to %loop.

I use this all the time...

One more example:

* this is often faster than dictionary.columns, esp. if RBDMS libraries are allocated ;
proc contents data=sashelp.cars (keep=_numeric_) out=contents noprint;
run;
proc sql noprint;
   select name into :vars separated by " " from contents order by varnum;
   drop table contents;
quit;

%macro code;
   &word=_&word
%mend;
%macro convert_to_char;
   &word=put(_&word,best32.-l);
%mend;

data test;
   set sashelp.cars (obs=10 rename=(%loop(&vars)));
   %loop(&vars,mname=convert_to_char)
   drop _:;
run;

https://github.com/scottbass/SAS/blob/master/Macro/loop.sas

https://github.com/scottbass/SAS/blob/master/Macro/nobs.sas

https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas  (if your metadata won't fit into a macro variable - rare)

HTH...

[noling]

I like the idea of putting the %loop into it's own macro. I often write out the loop 1x per rename/drop/convert, but it's much easier to read your way!

[Patrick]

@noling 

And if you've got SAS DI Studio then you can implement something like this using the Loop transformation which then also allows you to run such processes in parallel and/or SAS Grid enabled without any additional coding required.