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;
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;
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
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;
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
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.
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.
what do you mean?
It's a straight forward current-lag(current) within an id(by group)
Relax. Do not confuse 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: