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

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: 5,505

## Re: deriving lagged values with missing observations

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: 11,343

## Re: deriving lagged values with missing observations

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.
Posts: 4,173

## Re: deriving lagged values with missing observations

@sas_user_k

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: 11,343

## Re: deriving lagged values with missing observations

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.

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