topic Re: Loop Through Variable List to Create new variables with Macro in New SAS User

Loop Through Variable List to Create new variables with Macro

joebacon — Wed, 29 May 2019 15:28:07 GMT

Hi all,

It feels like it has been a while since I came for your sage advice.

I have a macro that looks like the following:

%macro yes(Exp);
data alldata_JB_052819;
set sub.alldata_jb_020819;
 PreResYr2nd6mo_&Exp =  PreResYr2nd6mo_&Exp + 1;
PostResYr1st6mo_&Exp = PostResYr1st6mo_&Exp + 1;
Prop&Exp = PostResYr1st6mo_&Exp/PreResYr2nd6mo_&Exp;
run;
%mend;
%yes(House);
%yes(Consume);
%yes(Durable);
%yes(Trans);
%yes(Health);
%yes(Insure);
%yes(Loan);
%yes(Tax);
%yes(Educa);
%yes(Gift);
%yes(Enter);
%yes(Recre);
%yes(FinPlan);
%yes(Legala);
%yes(AlcSpend);
%yes(ST2DENOM2);
%yes(ST2DENOM3);
%yes(ExpendTL);
%yes(FinPlSv);
%yes(IncomeTL);
%yes(ST2Oblig);

This macro is looping through each of these variables and then only creating the last variable in the new dataset. In this case, it is PropST2Oblig.

How do I make this macro loop through each of these variables and create a new variable (proportion of Post/Pre) for each instance?

For reference, the data set is quite large and in wide format. I want to create a proportion for each of the variables at each of the time intervals ( e.g., 12 1-month prop, 4 quarter-prop, 3 trimester-prop, etc.)

I am sure I could reduce the input somehow, but came looking for advice/what problem my macro has

Re: Loop Through Variable List to Create new variables with Macro

VDD — Wed, 29 May 2019 15:31:57 GMT

use a data step and do an append to add variable's to the original dataset.

Re: Loop Through Variable List to Create new variables with Macro

PaigeMiller — Wed, 29 May 2019 15:40:01 GMT

%macro yes(Exp);
data temp;
set sub.alldata_jb_020819;
 PreResYr2nd6mo_&Exp =  PreResYr2nd6mo_&Exp + 1;
PostResYr1st6mo_&Exp = PostResYr1st6mo_&Exp + 1;
Prop&Exp = PostResYr1st6mo_&Exp/PreResYr2nd6mo_&Exp;
run;
proc append base=temp new=all;
run;
%mend;

This is a simple way of getting everything into one data set, which I have cleverly named ALL., but you get a lot of missing values this way.

Probably a better way, which avoids the missing values problem:

%macro yes(Exp);
data alldata_JB_052819;
set sub.alldata_jb_020819;
%do i=1 %to %sysfunc(countw(&exp));
    %let thisexp=%scan(&exp,&i,%str( ));
 PreResYr2nd6mo_&thisExp =  PreResYr2nd6mo_&thisExp + 1;
PostResYr1st6mo_&thisExp = PostResYr1st6mo_&thisExp + 1;
Prop&thisExp = PostResYr1st6mo_&thisExp/PreResYr2nd6mo_&thisExp;
run;
%mend;
%yes(house consume durable) /* You type the rest of the variable names, I'm too lazy */

Re: Loop Through Variable List to Create new variables with Macro

joebacon — Wed, 29 May 2019 15:51:43 GMT

@PaigeMiller You crack me up. Thank you for taking the time to help me.

I attempted to implement the second solution, however, it is throwing the error: "ERROR 180-322: Statement is not valid or it is used out of proper order." when looping through the list of variables. House got inputted, but none of the other variables did.

This is the slightly modified code to include an "end" statement to the do-Loop.

%macro yes(Exp);
data alldata_JB_052819;
set sub.alldata_jb_020819;
%do i=1 %to %sysfunc(countw(&exp));
    %let thisexp=%scan(&exp,&i,%str( ));
 PreResYr2nd6mo_&thisExp =  PreResYr2nd6mo_&thisExp + 1;
PostResYr1st6mo_&thisExp = PostResYr1st6mo_&thisExp + 1;
Prop&thisExp = PostResYr1st6mo_&thisExp/PreResYr2nd6mo_&thisExp;
run;
%end;
%mend;
%yes(house consume durable Trans health Insure Loan Tax Educa Gift Enter REcre FinPlan Legala AlcSpend ST2DENOM2 ST2DENOM3 ExpendTl FinPlSv IncomeTL STOblig);

Any recommendations?

Re: Loop Through Variable List to Create new variables with Macro

Astounding — Wed, 29 May 2019 16:27:21 GMT

Move the RUN; statement to after the %END; statement.

Re: Loop Through Variable List to Create new variables with Macro

joebacon — Wed, 29 May 2019 16:51:04 GMT

Good catch. Silly mistake. Thank you. I must say that was rather Astounding...