topic Re: Do Looping into macro in SAS Enterprise Guide

Do Looping into macro

Andy5 — Tue, 03 Jan 2017 14:38:50 GMT

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.

Re: Do Looping into macro

Astounding — Tue, 03 Jan 2017 14:52:15 GMT

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?)

Re: Do Looping into macro

Andy5 — Tue, 03 Jan 2017 15:10:53 GMT

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.

Re: Do Looping into macro

Astounding — Tue, 03 Jan 2017 15:26:20 GMT

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 ...

Re: Do Looping into macro

Astounding — Tue, 03 Jan 2017 15:32:17 GMT

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 ...

Re: Do Looping into macro

Astounding — Tue, 03 Jan 2017 15:36:41 GMT

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.

Re: Do Looping into macro

Andy5 — Tue, 03 Jan 2017 18:07:24 GMT

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.