Calculating the year and quarter difference from row in SAS

[sandyzman1]

Hi All,

I am trying to get the difference between the dates (quarters) in consequent rows.  I have the following data: 

data have;
input ID year $ quarter $;
cards;

1 2012 2
1 2015 1
2 2011 1
2 2012 3
2 2015 4
3 2012 2
3 2013 3
4 2014 1
4 2014 2
5 2014 2
5 2015 4
;
run;

data have;
set have;
date=input(cats(year,'Q',quarter),yyq6.);
format date yyq6.;
run;

Tabel desired:

ID	year	quarter	date	difference
1	2012	2	2012Q2	.
1	2015	1	2015Q1	5
2	2011	1	2011Q1	.
2	2012	3	2012Q3	6
2	2015	4	2015Q4	9
3	2012	2	2012Q2	.
3	2013	3	2013Q3	5
4	2014	1	2014Q1	.
4	2014	2	2014Q2	1
5	2014	2	2014Q2	.
5	2015	4	2015Q1	2

Thanks.

Sandeep

[Kurt_Bremser · Accepted solution]

The value of the previous iteration of the data step can be retrieved with LAG, and date intervals can be calculated with INTCK.

[sandyzman1]

KurtBremser,

Thanks for your solution. It worked.

For the future reference, I used following code:

data temp;
set temp;
by ID;
lag1_value = lag(date);
if first.id then lag1_value = .;
format lag1_value yyq6.;
run;

data temp1;
set temp;
quarters=intck('qtr',lag1_value, date);
run;

Thanks.

[andreas_lds]

The values you want as difference don't match what i would expect, please explain. The difference between 2012Q2 and 2015Q1 should be 11

[sandyzman1]

Apology for the typo !! You are correct. It should have been 11 instead of 5. Thanks.