Loop through duplicate Id's and subtract row2-row1

[sghatak]

Hi,
I have data is following format
Id Date
1 201701
1 201711
2 201809
2 201812
3 201702
3 201708
3 201812

I need to group by ID's and find the difference between the dates, so my output looks like -
Id Date Difference
1 201701 0
1 201711 10
2 201809 0
2 201812 3
3 201702 0
3 201708 6
3 201812 4

data want;
_n_ =0;
do until(last.ID);
set have; by Id;
_n_+1;
if _n_ = _n_ - 1 then dateflag = date;
if _n_ = _n_ +1 then difference = dateflag - date;
end;
run;

[novinosrin · Accepted solution]

data have;
input Id Date yymmn6.;
format date date9.;
cards;
1 201701
1 201711
2 201809
2 201812
3 201702
3 201708
3 201812
;

data want;
set have;
by id;
k=lag(date);
if first.id then want=0;
else want=intck('month',k,date);
run;

@sghatak 

3 201708 6
3 201812 4 

In your sample is incorrect . The year should be either 2017 or Difference should be 16. Please review and  verify

[sghatak]

My Id is char, date is num, hence i am trying to subtract rows, thank you for quick response, appreciate it.

[novinosrin]

No problem with your ID being char or num. We aren't using that at all. Basically your date is a numeric date and what i understand is you are trying to compute the month intervals between two dates, hence I used the intck function. Please test. 

I really hope you are understanding the code and mostly importantly your requirement. 

---

@sghatak wrote:
My Id is char, date is num, hence i am trying to subtract rows, thank you for quick response, appreciate it.

---

[sghatak]

I got it. I modified the code to just the difference between k and date, hence i get the numbers i was hoping for, thank you, this was a simple solution rather than doing Do loop; appreciate timely help.

[sghatak]

one more thing forgot to add, i needed to group by Id's , so for iD = 1 subtract, ID=2, subtract until last.ID. Anyway , have to loop it for this. Thanks.

[novinosrin]

what do you mean?

It's a straight forward current-lag(current) within an id(by group)

Relax. Do not confuse 🙂