deriving lagged values with missing observations

Solved
Occasional Contributor
Posts: 19

deriving lagged values with missing observations

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
 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 1424 0.42 2013 3 Google 2321 0.34 2313 0.21 2013 4 Google 4525 0.11 1322 0.33 2014 1 Google 2063 0.24 5756 0.23 2014 2 Google 7423 0.25 2321 0.34 2014 4 Google 4241 0.77 4525 0.11 2015 1 Google 1123 0.23 2063 0.24 2015 2 Google 2323 0.55 7423 0.25 2015 3 Google 8824 0.15 . . 2013 1 Loreal 2314 0.23 . . 2013 2 Loreal 2322 0.11 . . 2013 3 Loreal 5552 0.02 . . 2014 1 Loreal 2400 0.15 2314 0.23 2014 2 Loreal 8723 0.23 2322 0.11 2014 3 Loreal 2123 0.32 5552 0.02 2014 4 Loreal 8523 0.25 . . 2015 1 Loreal 2421 0.13 2400 0.15 2015 3 Loreal 2342 0.23 2123 0.32 2015 4 Loreal 5663 0.42 8523 0.25

Accepted Solutions
Solution
‎10-25-2016 09:59 PM
Occasional Contributor
Posts: 19

Re: deriving lagged values with missing observations

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;``````

All Replies
Super User
Posts: 6,629

Re: deriving lagged values with missing observations

Posted in reply to sas_user_k

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.

Super User
Posts: 13,304

Re: deriving lagged values with missing observations

Posted in reply to sas_user_k

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.

Occasional Contributor
Posts: 19

Re: deriving lagged values with missing observations

Thank you and I just solved the issue on my own by using proc sql.
Respected Advisor
Posts: 4,672

Re: deriving lagged values with missing observations

Posted in reply to sas_user_k

@sas_user_k

Then please post your solution and close the thread as answered

Solution
‎10-25-2016 09:59 PM
Occasional Contributor
Posts: 19

Re: deriving lagged values with missing observations

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;``````
Super User
Posts: 13,304

Re: deriving lagged values with missing observations

Posted in reply to sas_user_k

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?

☑ This topic is solved.

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

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