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

Hi. I would like to append fourth-quarter lagged values (e.g. asset and roa values) for each company by year into existing data with the form like below:

 

data temp;
input year quarter company $8-13 asset roa;
datalines;
2012 1 Google 1424 0.42
2012 2 Google 2231 0.23
2012 3 Google 2313 0.21
2012 4 Google 1322 0.33
2013 1 Google 5756 0.23
2013 3 Google 2321 0.34
2013 4 Google 4525 0.11
2014 1 Google 2063 0.24
2014 2 Google 7423 0.25
2014 4 Google 4241 0.77
2015 1 Google 1123 0.23
2015 2 Google 2323 0.55
2015 3 Google 8824 0.15
2012 1 Loreal 2314 0.23
2012 2 Loreal 2322 0.11
2013 3 Loreal 5552 0.02
2014 1 Loreal 2400 0.15
2014 2 Loreal 8723 0.23
2014 3 Loreal 2123 0.32
2014 4 Loreal 8523 0.25
2015 1 Loreal 2421 0.13
2015 2 Loreal 4524 0.12
2015 3 Loreal 2342 0.23
2015 4 Loreal 5663 0.42
;
run;

And the desirable output data should look like the one shown below, and the point to be cautious is the fact that there are some missing quarterly values, which prevents from using conventional 'lag' functions for deriving fourth-quarter lagged values for each observation. Thank you!

 

SAS Output

year  quarter  company  asset  roa  asset_t_1  roa_t_1
20121Google14240.42..
20122Google22310.23..
20123Google23130.21..
20124Google13220.33..
20131Google57560.2314240.42
20133Google23210.3423130.21
20134Google45250.1113220.33
20141Google20630.2457560.23
20142Google74230.2523210.34
20144Google42410.7745250.11
20151Google11230.2320630.24
20152Google23230.5574230.25
20153Google88240.15..
20131Loreal23140.23..
20132Loreal23220.11..
20133Loreal55520.02..
20141Loreal24000.1523140.23
20142Loreal87230.2323220.11
20143Loreal21230.3255520.02
20144Loreal85230.25..
20151Loreal24210.1324000.15
20153Loreal23420.2321230.32
20154Loreal56630.4285230.25

 

1 ACCEPTED SOLUTION

Accepted Solutions
sas_user_k
Obsidian | Level 7

I just used 'proc sql' with some conditions like this:

proc sql;
  create table temp_lagged
  as select a.*, b.roa as roa_lagged
  from temp a left join temp b
  on a.company=b.company and a.year=b.year+1 and a.quarter=b.quarter;
quit;

View solution in original post

6 REPLIES 6
Astounding
PROC Star

If your data set is small enough that you can sort it a couple of times, you could try this approach:

 

proc sort data=have;

   by company quarter year;

run;

 

data want;

   set have;

   by company quarter year;

   prior_roa = lag(roa);

   prior_asset = lag(asset);

   year_change = dif(year);

   if first.quarter=0 and year_change=1 then do;

      asset_t_1 = prior_asset;

      roa_t_1 = prior_roa;

   end;

   drop prior_roa prior_asset year_change;

run;

 

proc sort data=want;

   by company year quarter;

run;

 

It's untested code at this point, so you'll have to try it and see.

ballardw
Super User

I think you need to explicitly state your rule(s) for selecting which lag when the "4th" doesn't match the current quarter number. The one before or after? Always?

For instance,If the code is at a quarter = 1, year=2014 and the 1 from the previous year is missing do you want the quarter 2 from year=2013 or the quarter 4 of year=2012.

And if the "first" quarter of year in the data is 2 and the prior i2 quarter 3 of the previous year (missing two quarters in sequence at the year break) what result do you want?

What do you want if two quarters are missing in the previous "4"?

Or 3 missing?

Or specifically state that there is never a year with two quarters missing, or 3.

sas_user_k
Obsidian | Level 7
Thank you and I just solved the issue on my own by using proc sql.
Patrick
Opal | Level 21

@sas_user_k

Then please post your solution and close the thread as answered

sas_user_k
Obsidian | Level 7

I just used 'proc sql' with some conditions like this:

proc sql;
  create table temp_lagged
  as select a.*, b.roa as roa_lagged
  from temp a left join temp b
  on a.company=b.company and a.year=b.year+1 and a.quarter=b.quarter;
quit;
ballardw
Super User

Your posted solution does not match your posted example.

In your example desired result you have the line

2014 2 Google 7423 0.25 2321 0.34

and the 2321 and 0.34 come from Year 2013 and quarter 3.

 

So should your problem have been better stated as matching to the previous year's same quarter when availble?

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1721 views
  • 2 likes
  • 4 in conversation