Get the day difference when the variable is the same in the group

ttqkroe — Mon, 28 Nov 2022 17:09:46 GMT

Hi everyone, This is what the dataset looks like

ID	DATE	VALUE
1	2022-11-20	10
1	2022-11-22	10
2	2022-11-12	20
2	2022-11-18	20
2	2022-11-25	20
3	2022-11-21
3	2022-11-24
3	2022-11-27

I would like to have the result like this

ID	DATE	VALUE	DIF
1	2022-11-20	10
1	2022-11-22	10	2
2	2022-11-12	20
2	2022-11-18	20	6
2	2022-11-25	20	13
3	2022-11-21
3	2022-11-24
3	2022-11-27

So if VALUE is same in the same group which is the ID, then I need get the difference of Date between the first date in the group and the other date in the group. And if VALUE is blank, then I do not need the difference. I have tried use "if value ^=. and value ne first.value" then get value difference, but it will get wrong answer. Please help!

Thanks so much

Re: Get the day difference when the variable is the same in the group

PeterClemmensen — Mon, 28 Nov 2022 18:01:39 GMT

Try this

data have;
input ID DATE :yymmdd10. VALUE;
infile datalines missover;
format DATE yymmdd10.;
datalines;
1 2022-11-20 10 
1 2022-11-22 10 
2 2022-11-12 20 
2 2022-11-18 20 
2 2022-11-25 20 
3 2022-11-21    
3 2022-11-24    
3 2022-11-27    
;

data want(drop = d);
   set have;
   by ID;
   if first.id then d = date;
   if VALUE & first.ID = 0 then dif = date - d;
   retain d;
run;

Result:

ID  DATE        VALUE  dif
1   2022-11-20  10     .
1   2022-11-22  10     2
2   2022-11-12  20     .
2   2022-11-18  20     6
2   2022-11-25  20     13
3   2022-11-21  .      .
3   2022-11-24  .      .
3   2022-11-27  .      .

topic Re: Get the day difference when the variable is the same in the group in SAS Programming

Get the day difference when the variable is the same in the group

Re: Get the day difference when the variable is the same in the group