BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Andy5
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Astounding
PROC Star

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

Andy5
Calcite | Level 5

 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.

Astounding
PROC Star

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

 

Astounding
PROC Star

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

Astounding
PROC Star

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.

Andy5
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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