Appending Data Set in Macro Loop

[mgilbert]

This is my first post, so apologies if I'm missing something critical. Have spent time on Google and searching the SAS Community boards, but have not been able to find a solution (this one and this one come close though).

This is what's going on:

• I have a data set with 17 variables, some observations are missing datum
• I would like to create a macro that will loop over the data set and create a new flag variable for each variable in the data set
• The newly created flag variable(s) will be populated with a 0 or 1 depending on if the observation in the parent variable is missing datum

My interest in using a macro is because I could apply this to a variety of data sets, rather than typing everything out... This is what I have so far:

**********************************************************************;
*       Moneyball;
*       Last updated: 2015-10-04 by MJG;
**********************************************************************;

*       Shorten data name, save to work library;
data MB;
        set mydata.moneyball;
run; quit;
 
***********************************;
*       PROC CONTENTS;
***********************************;
*       List out the column names and data types for the data set;
proc contents data = MB out = MB_Contents;
run; quit;
 
*       Drop unnecessary variables gained from PROC CONTENTS;
data MB_Contents;
        set MB_Contents(keep = name type length varnum format formatl informat 
informl just npos nobs);
run; quit;
 
*       View contents of data set, more info than PROC CONTENTS output;
proc print data = MB_Contents;
run; quit;
 
**********************************************************************;
*       Missing Flags;
**********************************************************************;
 
%let data_og = MB;
%let data_new = MB_MF;
%let contents = MB_Contents;
%let varname = name;
 
*       Macro for missing flags;
%macro MB_Missing(varname);
        data &data_new.;
                set &data_og.;
                        if missing(&varname.) then &varname._MF = 1;
                                else &varname._MF = 0;
        run; quit;
%mend;
 
*       Create missing flag variables and populate;
data &data_new.;
        do i = 1 to num;
                set &contents. nobs = num;
                        call execute('%MB_Missing('||name||')');
        end;
run; quit;

From what I can tell (via the SAS log), the loop actually works and creates the flag variables as intended. The only hang up is that when I do a PROC PRINT of the resulting data set, MB_MF, only the last flag variable created is in there, the rest are not. So my suspicion is that I'm missing an APPEND or even PUT (?) somewhere in there?

I am fairly new to SAS so if there's something in my code that feels like nails on a chalkboard, please let me know!

Thanks,

Michael

[PGStats · Accepted solution]

You want each iteration to add a new variable, so you should be overwriting the dataset, not starting from the original on each iteration.

*       Macro for missing flags;
%macro MB_Missing(varname);
data &data_og.;
set &data_og.;
&varname._MF = missing(&varname.);
run;
%mend;
 
*       Create missing flag variables and populate;
data _null_;
set &contents. ;
call execute('%MB_Missing('||name||');');
end;
run;

(untested)

Note: quit; is ignored after run; except for interactive procedures such as proc reg that accept multiple run; statements.

[mgilbert]

Thank you! Worked perfect, and learned a new way to do the IF-THEN-ELSE logic. Greatly appreciated.

If I wanted to preserve MB (data_og), and simply create a new data set with these additional variables is the best way to do that a data step outside of the macro and populate step?

Michael

[PGStats]

Copy MB to MB_MF and do the update from there.