CODEWITHSUNDEEP
sas
immaprogrammingnoob
immaprogrammingnoob

Reputation: 167

How to code a macro that will iterate through a list of variables and return those that are missing

I'm still pretty new to SAS coding and I'm not great at loops. I want to code a macro that will iterate through a vector of variables and return a table of the 'study_id' of those that are missing this variable. Ideally, the macro would then append each list into one final table.

I know that I need a loop that iterates from 1 to the length of my vector of variables. I've also tested the sql step on a single variable and it works. Here's what I have, along with a truncated data set to reproduce the problem:

data test;
input Study_ID married_partner $ PT_Working $;
cards;
1 Yes Yes
2 No  No 
3 Yes .
5 Yes No 
6 Yes No 
8 Yes Yes
9 . No 
10 Yes No 
11 Yes No 
12 Yes No 
13 . No 
14 Yes No 
15 No No 
17 Yes . 
19 Yes No 
20 Yes No 
21 Yes No 
;
run;

%let var=married_partner PT_Working;
%macro missing(data=, list=, var=);
 do i = 1 to dim(&var);
    proc sql;
    create table missing_&var as
    select &list
    from &data
    where missing(&var);
    quit;
 end;
%mend;

%missing(data=PT_BASELINE_ALLPT, list=Study_ID, var=&var)

I'm getting the following error:

61          missing_married_partner PT_Working
                                    __________
                                    78
                                    202
NOTE: Line generated by the macro variable "VAR".
61         married_partner PT_Working
                           __________
                           22
ERROR 78-322: Expecting a '.'.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

Where am I going wrong and what further code should I add to combine all of these into one table?

Thanks for any help

Upvotes: 0

Views: 121

Answers (1)

Tom
Tom

Reputation: 51581

You don't really need macro code for this problem. Remember that the purpose of macro code is to generate SAS code so first figure out what SAS code you want to run before trying to use macro logic to generate it.

To process a series of variables you can usually use an array. Although they do need to be of the same type (numeric or character).

If you just want to find observations with missing values on any of the variables you don't even need an array. The CMISS() function will work for both numeric and character variables. So this step will find all of the observations with any missing values of the two variables listed.

 data want ;
   set have;
   if cmiss(of married_partner PT_Working);
 run;

If you want it more flexible you could use a macro variable for the variable list.

 data want ;
   set have;
   if cmiss(of &varlist);
 run;

If would be harder to do in PROC SQL since that does not support the use of variable lists, including the OF keyword. Instead you would need to put commas between the variable names.

 create table want as select * from have where cmiss(married_partner, PT_Working);

Upvotes: 3

Related Questions