Desktop productivity for business analysts and programmers

Do Looping into macro

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Do Looping into macro

I am trying to create lead/lag variables from a data set.  My goal is to take all variables and create a certain number of lead/lag variables to be added to the data set.  I have a macro that creates the lead/lags for one variable.  I am having difficulty looping the variables in my data set through the macro.

 

* Here is some sample data;
data mydata;
input time var1 var2 var3;
datalines;
1 1.2 54 100
2 1.8 50 90
3 1.5 56 95
4 2.1 67 92
5 2.5 75 110
6 1.9 77 104
7 2.3 84 108
8 2.1 81 112
;
options validvarname=v7;
run;

*Here is part of the macro I created;
%macro laglead(mydata=, var=);
	/*set mydata;*/
	set &mydata(firstobs=2 keep=&var rename=(&var=Lead1_&var) )
		&mydata( obs=1 drop=_all_);

	%let name = Lag1_&var;
	&name = ifn(first.&var, (.), lag(&var));

	%let name = fut_chg1_&var;
	&name = lead1_&var-&var;

	%let name = past_chg1&var;
	&name = &var-lag1(&var);

%mend laglead;

*Loop variables through macro;
data createlead;
	set mydata;

array d var1--var3;
do over d;
%laglead(mydata=mydata,var=d);
end;

run;

This returns 2 errors:

 

ERROR: Variable d is not on file WORK.MYDATA.

ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.MYDATA.

 

Any thoughts or suggestions would be appreciated.

 

I am using EG 7.13 and yes I have ETS.  I couldn't get proc expand to work.


Accepted Solutions
Solution
‎01-03-2017 01:06 PM
Respected Advisor
Posts: 4,950

Re: Do Looping into macro

Part 3, later changes within the macro.

Similarly, you will need to loop through every variable for the data manipulation logic.

 

%do i=1 %to %sysfunc(countw(&varlist));

   %let varname = %scan(&varlist, &i);

   lag1_&varname = lag(&varname);

   past_chg1_&varname = dif(&varname);

   fut_chg1_&varname = lead1_&varname - &varname;

%end;

 

You may need to confirm that the new variables have the values you intended.

View solution in original post


All Replies
Respected Advisor
Posts: 4,950

Re: Do Looping into macro

[ Edited ]

Up to now, you would specify (for example):

 

var=var1

 

Is  your list of variables short enough that you can list all the names, such as:

 

var_list = var1 var2 var3

 

Or do you need to automate the part about discovering the names of the variables?

 

(Secondarily, are all your variable names short enough that you can add "Lead1_" to the beginning of the name without exceeding 32 characters?)

New Contributor
Posts: 3

Re: Do Looping into macro

 I would like to be able to automate the discovery of the variable names.  I will be using different data sets with different numbers of variables.  However, I can easily reorder the variables and do use an array such as var1--var3.  Also, I have to do some upfront cleaning of the variable names (removal of spaces) upfront.  I can easily trim the names down so I do not run into a character limit issue.

Respected Advisor
Posts: 4,950

Re: Do Looping into macro

Andy,

 

Somehow your message got deleted and reappeared, and my solution couldn't be posted.  I'll take it in stages this time to make sure I don't lose too much of what I am trying to post.

 

First, since you are trying to extract the variable names (only the numeric ones, I assume), you will need to do that ahead of time.  It looks like you want  your macro to follow the DATA statement, so here is what goes before the DATA statement:

 

proc contents data=have (keep=_numeric_) out=_contents_ (keep=name) noprint;

run;

proc sql noprint;

select strip(name) into : varlist separated by ' ' from _contents_;

quit;

 

That gives  you a macro variable with the names of all variables that need to be processed.

 

More to follow ...

 

Respected Advisor
Posts: 4,950

Re: Do Looping into macro

Part 2, changes inside the macro.  I'll try to capture your original intentions without changing the formulas too much, but you may need to alter some of the code here.

 

Basically, you need a way to loop through every variable name that needs to be processed.  Here is the idea.

 

%local i varname;

set &mydata (firstobs=2 keep=&varlist rename=(

   %do i=1 %to %sysfunc(countw(&varlist));

      %let varname = %scan(&varlist, &i);

      &varname = lead1_&varname

   %end;

   ) )  &mydata (obs=1 drop=_all_);

 

That would complete the SET statement with all the renames.  More to follow ...

Solution
‎01-03-2017 01:06 PM
Respected Advisor
Posts: 4,950

Re: Do Looping into macro

Part 3, later changes within the macro.

Similarly, you will need to loop through every variable for the data manipulation logic.

 

%do i=1 %to %sysfunc(countw(&varlist));

   %let varname = %scan(&varlist, &i);

   lag1_&varname = lag(&varname);

   past_chg1_&varname = dif(&varname);

   fut_chg1_&varname = lead1_&varname - &varname;

%end;

 

You may need to confirm that the new variables have the values you intended.

New Contributor
Posts: 3

Re: Do Looping into macro

This works great!  Thank you for all three parts.  I don't understand all of the code yet but I will take some time to learn exactly what is going on.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 220 views
  • 0 likes
  • 2 in conversation