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

I have panel data by firm id and fiscal quarter. I am trying to create lags of my key variables.

Unfortunately my macro is not working. Can someone plea help.

 

data WORK.TEST;
  infile datalines dsd truncover;
  input id:$6. FQTR:$6. var1:F18.4 var2:F18.4 var3:F18.4;
  format var1 F18.4 var2 F18.4 var3 F18.4;
datalines4;
001000,3926,,,0.0000
001000,4018,,,
001000,4108,,,
001000,4199,,,
001000,4291,,,0.0000
001000,4383,,,
001000,4474,,,
001000,4565,,,
001000,4657,,,0.0000
001000,4749,,,
001000,4839,,,
001000,4930,,,
001000,5022,,,0.0000
001000,5114,,,
001000,5204,,,
001000,5295,,,
001000,5387,,,-0.5800
001000,5479,,,0.0000
001000,5569,,,0.0000
001000,5660,,,0.0000
001000,5752,,,0.0000
001000,5844,,,0.0000
001000,5935,,,0.0000
001000,6026,,,0.0000
001000,6118,,,0.0000
001000,6210,,,0.0000
001000,6300,,,
001000,6391,,,
001000,6483,,,
001000,6575,,,0.0000
001001,8582,,,0.0000
001001,8674,,,0.0000
001001,8766,,,0.0000
001001,8857,,,0.0000
001001,8948,,,0.0000
001001,9040,,,0.0000
001001,9132,,,0.0000
001001,9222,,,0.0000
001001,9313,,,0.4700
001001,9405,,,0.1190
001001,9497,,,0.0000
001003,8674,,,0.0000
001003,8766,,,0.0000
001003,8857,,,0.0000
001003,8948,,,0.0000
001003,9040,,,0.0000
001003,9132,,,0.0000
001003,9222,,,0.0000
001003,9313,,,0.0000
001003,9405,,,0.0000
001003,9497,,,0.0000
001003,9587,,,0.0000
001003,9678,,,0.0000
001003,9770,,,0.0000
001003,9862,,,0.0000
001003,9952,,,0.0000
001003,10043,,,0.0000
001003,10135,,,0.0000
001003,10227,,,0.0000
001003,10318,,,0.0000
001003,10409,,,0.0000
001003,10501,,,-3.5000
001003,10593,,,
001003,10683,,,
001004,4291,,,0.0000
001004,4383,,,
001004,4474,,,
001004,4565,,,
001004,4657,,,0.0000
001004,4749,,,
001004,4839,,,
001004,4930,,,
001004,5022,,,0.0000
001004,5114,,,
001004,5204,,,
001004,5295,,,
001004,5387,,,0.0000
001004,5479,,,0.0000
001004,5569,,,0.0000
001004,5660,,,0.0000
001004,5752,,,0.0000
001004,5844,,,0.0000
001004,5935,,,0.0000
001004,6026,,,0.0000
001004,6118,,,0.0000
001004,6210,,,0.0000
001004,6300,,,0.0000
001004,6391,,,0.0000
001004,6483,,,0.0000
001004,6575,,,0.0000
001004,6665,,,0.0000
001004,6756,,,0.0000
001004,6848,,,0.0000
001004,6940,,,0.0000
001004,7030,,,0.0000
001004,7121,,,0.0000
001004,7213,,,0.0000
001004,7305,,,0.0000
001004,7396,,,0.0000
001004,7487,,,0.0000
;;;;

Here is the macro:

 

***CREATE LAGS OFKEY VARIABLES***;
%macro lag(vars, lags);
      %let m = %sysfunc(countw(&vars));
      %do i=1 %to &m;
           %let var = %scan(&vars,&i);
           %do j=1 %to &lags;
                 %do;
                 if first.id then &var._lag&j=.;
                      &var._lag&j = lag&j(&var);
                 %end;
           %end;
      %end;
%mend lag;

data test;
set test;   
by id;
%lag(var1 var2 var3, 1);
%lag(var1 var2 var3, 2); 
run;
1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @Agent1592 

 

I think you are on the right track, but your code needs a few modifications.

 

I can't see why the use of RETAIN would be a better solution, and it is definitely not easier to code in a macro (I tried), so - based on Tom's suggestions - I think the following code will work the way you want.

 

There is a slight discrepancy between your macro code and your usage example, because your macro is coded to loop over lags from 1 to the specified max, but your example has two calls in the same data step with lags=1 and lags=2. The first call is unnecessary and only writes redundant code.

 

%macro lag(vars, lags);
	%let m = %sysfunc(countw(&vars));
	row + 1;
	if first.id then row = 1;
	%do i = 1 %to &m;
		%let var = %scan(&vars,&i);
		%do j = 1 %to &lags;
			&var._lag&j = lag&j(&var);
			if row <= &j then &var._lag&j = .;
		%end;
	%end;
%mend;

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

These two statements are in the wrong order.

if first.id then &var._lag&j=.;
&var._lag&j = lag&j(&var);

Plus if you really want to do it this way then I think you will need keep track of how many observations you have seen for this ID.

So you want to have your macro generate code that looks like:

row+1;
if first.id then row=1;
VAR1_lag1=lag1(VAR1);
VAR2_lag2=lag2(VAR2);
....
if row<=1 then call missing(VAR1_lag1,VAR2_lag1);
if row<=2 then call missing(VAR1_lag2,VAR2_lag2);
...

I am not a big fan of using the new IFN/IFC functions because they are much more confusing code than normal IF/THEN/ELSE, but it would probably be easier for the macro to generate code like:

&var._lag&j=ifn(row<=&j,.,lag&j(&var));

instead of the all of the IF statements I suggested above.

 

See if you can mosidy your macro to generate code to calculate the ROW variable and generate those assignment statements using IFN() function to create the new variables.

 

The better solution would be to just use RETAIN instead of LAG().  Perhaps someone can post an answer that does that.

ErikLund_Jensen
Rhodochrosite | Level 12

Hi @Agent1592 

 

I think you are on the right track, but your code needs a few modifications.

 

I can't see why the use of RETAIN would be a better solution, and it is definitely not easier to code in a macro (I tried), so - based on Tom's suggestions - I think the following code will work the way you want.

 

There is a slight discrepancy between your macro code and your usage example, because your macro is coded to loop over lags from 1 to the specified max, but your example has two calls in the same data step with lags=1 and lags=2. The first call is unnecessary and only writes redundant code.

 

%macro lag(vars, lags);
	%let m = %sysfunc(countw(&vars));
	row + 1;
	if first.id then row = 1;
	%do i = 1 %to &m;
		%let var = %scan(&vars,&i);
		%do j = 1 %to &lags;
			&var._lag&j = lag&j(&var);
			if row <= &j then &var._lag&j = .;
		%end;
	%end;
%mend;
Tom
Super User Tom
Super User

You can eliminate the need for RETAIN by adding a DO loop around the SET statement.

So if you have this input:

%let nlags=3;
%let varlist=var1 var2 ;

You can generate this code.

%let nvars=%sysfunc(countw(&varlist));

data want ;
  do until (last.id);
    set have ;
    by id;
    array lags[&nvars,0:&nlags]
        var1 var1_lag1-var1_lag&nlags
        var2 var2_lag1-var2_lag&nlags
    ;
    output;
    do i=1 to &nvars; do j=&nlags to 1 by -1;
      lags[i,j] = lags[i,j-1];
    end;end;
  end;
  drop i j;
run;

Now the only thing that needs macro logic to generate it is the list of variables for the ARRAY statement.

    array lags[&nvars,0:&nlags]
%do i=1 %to &nvars;
  %let var=%scan(&varlist,&i);
        &var. &var._lag1-&var.lag&nlags
%end;
    ;
yabwon
Onyx | Level 15

HI,

 

Maybe try something like this:

 

data WORK.TEST;
  infile datalines dsd truncover dlm = ",";
  input id:$6. FQTR:$6. var1 var2 var3;
datalines4;
001000,3926,1,2,3
001000,4018,4,5,6
001000,4108,7,8,9
002000,3926,1,2,3
002000,4018,4,5,6
002000,4108,7,8,9
002000,3926,10,11,12
003000,4018,4,5,6
003000,4108,7,8,9
;;;;
run;


options mprint;

%macro lag(vars, lags);
  %local m;
  %let m = %sysfunc(countw(&vars));
  array var&lags._[*] &vars.;
  array lag&lags._[&m.] %sysfunc(tranwrd(&vars, %str( ), _lag&lags.%str( )))_lag&lags.;  
  
  if first.id then _cnt_&lags._=0; drop _cnt_&lags._;
  _cnt_&lags._+1;
  
    do _N_=1 to dim(var&lags._);
        lag&lags._[_N_] = lag&lags.(var&lags._[_N_]);
    end;
    if _cnt_&lags._ <= &lags.  then 
      call missing(of lag&lags._[*]);

%mend lag;

data test2;
set test;   
by id;
%lag(var1 var2 var3, 1);
%lag(var1 var2 var3, 2);
run;

 

it uses arrays so it is easier to maintain.

 

all the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 757 views
  • 3 likes
  • 4 in conversation