SAS loop through datasets

Question

I have multiple tables in a library call snap1:

cust1, cust2, cust3, etc

I want to generate a loop that gets the records' count of the same column in each of these tables and then insert the results into a different table.

My desired output is:

Table Count

cust1 5,000
cust2 5,555
cust3 6,000

I'm trying this but its not working:

%macro sqlloop(data, byvar);
proc sql noprint;
select &byvar.into:_values SEPARATED by '_'
from %data.;
quit;
data_&values.;
set &data;
select (%byvar);

%do i=1 %to %sysfunc(count(_&_values.,_));
%let var = %sysfunc(scan(_&_values.,&i.));
output &var.;
%end;
end;

run;
%mend;

%sqlloop(data=libsnap, byvar=membername);

Answer 1

First off, if you just want the number of observations, you can get that trivially from dictionary.tables or sashelp.vtable without any loops.

proc sql;
  select memname, nlobs
    from dictionary.tables
    where libname='SNAP1';
quit;

This is fine to retrieve number of rows if you haven't done anything that would cause the number of logical observations to differ - usually a delete in proc sql.

Second, if you're interested in the number of valid responses, there are easier non-loopy ways too.

For example, given whatever query that you can write determining your table names, we can just put them all in a set statement and count in a simple data step.

%let varname=mycol; *the column you are counting;
%let libname=snap1;


proc sql;
  select cats("&libname..",memname) 
    into :tables separated by ' '
    from dictionary.tables
    where libname=upcase("&libname.");
quit;

data counts;
  set &tables. indsname=ds_name end=eof; *9.3 or later;
  retain count dataset_name;
  if _n_=1 then count=0;
  if ds_name ne lag(ds_name) and _n_ ne 1 then do;
    output;
    count=0;
  end;
  dataset_name=ds_name;
  count = count + ifn(&varname.,1,1,0);  *true, false, missing;  *false is 0 only;
  if eof then output;
  keep count dataset_name;
run;

Macros are rarely needed for this sort of thing, and macro loops like you're writing even less so.

If you did want to write a macro, the easier way to do it is:

• Write code to do it once, for one dataset
• Wrap that in a macro that takes a parameter (dataset name)
• Create macro calls for that macro as needed

That way you don't have to deal with %scan and troubleshooting macro code that's hard to debug. You write something that works once, then just call it several times.

proc sql;
  select cats('%mymacro(name=',"&libname..",memname,')') 
    into :macrocalls separated by ' '
    from dictionary.tables
    where libname=upcase("&libname.");
quit;

&macrocalls.;

Assuming you have a macro, %mymacro, which does whatever counting you want for one dataset.

Answer 2

* Updated *

In the future, please post the log so we can see what is specifically not working. I can see some issues in your code, particularly where your macro variables are being declared, and a select statement that is not doing anything. Here is an alternative process to achieve your goal:

Step 1: Read all of the customer datasets in the snap1 library into a macro variable:

proc sql noprint;
    select memname
    into :total_cust separated by ' '
    from sashelp.vmember
    where upcase(memname) LIKE 'CUST%'
          AND upcase(libname) = 'SNAP1';
quit;

Step 2: Count the total number of obs in each data set, output to permanent table:

 %macro count_obs;
      %do i = 1 %to %sysfunc(countw(&total_cust) );
           %let dsname = %scan(&total_cust, &i);

           %let dsid=%sysfunc(open(&dsname) );
           %let nobs=%sysfunc(attrn(&dsid,nobs) );
           %let rc=%sysfunc(close(&dsid) );

           data _total_obs;
                length Member_Name $15.;
                Member_Name = "&dsname";
                Total_Obs = &nobs;

                format Total_Obs comma8.; 
           run;

           proc append base=Total_Obs
                       data=_total_obs;
           run;
     %end;

     proc datasets lib=work nolist;
         delete _total_obs;
     quit;

 %mend;
 %count_obs;

You will need to delete the permanent table Total_Obs if it already exists, but you can add code to handle that if you wish.

If you want to get the total number of non-missing observations for a particular column, do the same code as above, but delete the 3 %let statements below %let dsname = and replace the data step with:

data _total_obs;
     length Member_Name $7.;
     set snap1.&dsname end=eof;
     retain Member_Name "&dsname";

     if(NOT missing(var) ) then Total_Obs+1;

     if(eof);

     format Total_Obs comma8.;
run;

---

(Update: Fixed %do loop in step 2)