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.
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
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...
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: