proq sql macro; loop through list

Question

I have a macro that looks for variable names in a given dataset. If the variable name the macro is looking for is missing, the missing variable's name gets added to a table:

%macro miss(ds, var);
    %local rc dsid result;
    %let dsid=%sysfunc(open(&ds));
    %if %sysfunc(varnum(&dsid,&var)) > 0 %then %do;
        %put &var present;
    %end;
    %else %do;
        insert into work.compare(missing) values("&var")
    %end;
%mend;

proc datasets library=work nolist nodetails;
    delete compare;
run;

proc sql;
    create table work.compare (missing char(15));
%miss(ctr.panup_in, u_name);
quit;

proc print noobs data=work.compare;
run;

This check needs to be run for 55 different variable names. At the moment, I just have every single one listed as a

%miss(ctr.panup_in, varname);

line.

For practical reasons, I would like to specify the list of variables as a list, eg %let dictionary=var1 var2 var3 etc. My struggle right now is to find a way for the macro to loop through a variable list. Everything I've tried so far leads to a "Stament is not valid" error for the

insert into work.compare(missing) values("&var")

command.

Does anyone have any advice on how to do this?

Answer 1

This Loops thoough a set of variables where the variables are separated by "|". Any other delimiter can be used and specified in the scan function as well.

%macro loop(varlist);
%let i=1;
%do %while (%scan(&varlist, &i, |) ^=%str());
%let var=%scan(&varlist, &i, |); 
%put &var;

*rest of SAS code goes here;

*Increment counter;
%let i=%eval(&i+1);
%end;
%mend;
%let temp=a|b|c|d|e;
%loop(&temp);

Answer 2

Do you really need to check. You could define a zero obs data set of all variable you want to add if they're missing and use an unexecuted SET to include them.

*All variables that you might want to add;
data master0;
   attrib a length=8;
   attrib b length=$15;
   attrib c length=$15;
   stop;
   call missing(of _all_);
   run;

*Subset of the variable in master0;
data a;
   do a = 1 to 10;
      output;
      end;
   retain X 1;
   run;

*Create new data with master + a variables;
data a_all;
   set a;
   if 0 then set master0;
   run;
proc print;
   run;

You can get rid of variables that you don't want using modify to update to a master with all the variables.

*Create new data with with only wanted variables from master0;
data a_all;
   stop;
   set master0;
   run;
data a_all;
   if 0 then modify a_all;
   set a;
   output;
   run;

Answer 3

Another option is to have your variables stored in a table - it's likely they already are in one. Perhaps dictionary.columns?

In fact, odds are you can simply do this whole process through dictionary.columns. For example:

%let varlist=name age novar;

data values_to_check;
  length name $32;
  do _i = 1 to countw("&varlist.");
    name= scan("&varlist.",_i);
    output;
  end;
run;


proc sql;

  create table compare as
    select V.name as missing
    from values_to_check V
    left join
     (Select * from dictionary.columns 
      where memname='CLASS' and libname='SASHELP') D      
      on upcase(V.name)=upcase(D.name)
    where D.name is null
    ;
  quit;

That is of course easier if you don't have to create the dataset, if it already exists - either as the list of variables in another dataset that does exist, or just create it directly via datalines or similar. Odds are you have some data piece that has this information, though, not just hardcoded %let statement.

An example, which identifies the variables in CLASSFIT not in CLASS (both in SASHELP library):

proc sql;    
  create table compare as
    select V.name as missing
    from  
      (Select * from dictionary.columns 
      where memname='CLASSFIT' and libname='SASHELP')  V
    left join
     (Select * from dictionary.columns 
      where memname='CLASS' and libname='SASHELP') D      
      on upcase(V.name)=upcase(D.name)
    where D.name is null
    ;
  quit;

Answer 4

If you really want a table with the names that are not in your dataset then you could just use a data step and eliminate the complexity of using macro or PROC SQL.

%let ds=sashelp.class;
%let varlist=age sex gender ;
data compare;
  length missing $32 ;
  dsid=open("&ds");
  do i=1 to countw("&varlist");
    missing = scan("&varlist",i);
    if not varnum(dsid,missing) then output;
  end;
  rc=close(dsid);
  stop;
  keep missing;
run;

Now where a macro solution might be useful is if you wanted to pass in the list and get out another list, without generating any code.

%macro miss(ds, varlist);
%local dsid i var result;
%let dsid=%sysfunc(open(&ds));
%if (&dsid) %then %do;
  %do i=1 %to %sysfunc(countw(&varlist));
    %let var=%scan(&varlist,&i);
    %if not %sysfunc(varnum(&dsid,&var)) %then %let result=&result &var;
  %end;
  %let rc=%sysfunc(close(&dsid));
%end;
%else %let result=&varlist;
&result.
%mend miss;

That way you could call the macro in the middle of a statement, like a function call.

%put Missing variables are: %miss(sashelp.class,age sex gender);