Appending Data Set in Macro Loop

mgilbert — Mon, 05 Oct 2015 01:59:44 GMT

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

Re: Appending Data Set in Macro Loop

PGStats — Mon, 05 Oct 2015 03:26:25 GMT

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.

Re: Appending Data Set in Macro Loop

mgilbert — Mon, 05 Oct 2015 11:48:07 GMT

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

Re: Appending Data Set in Macro Loop

PGStats — Mon, 05 Oct 2015 14:55:19 GMT

Copy MB to MB_MF and do the update from there.

topic Appending Data Set in Macro Loop in SAS Procedures

Appending Data Set in Macro Loop

Re: Appending Data Set in Macro Loop

Re: Appending Data Set in Macro Loop

Re: Appending Data Set in Macro Loop