topic Re: Group identical ids and indicate first and last date of an incident per id in SAS Programming

Group identical ids and indicate first and last date of an incident per id

AnnaNZ — Fri, 09 Jun 2017 02:14:44 GMT

I have a dataset with many identical IDs, which were taken up at different times (datetime 18).

I want to know the earlierst and the last time that an ID was taken up ( and calculate the timeinterval).

So some REFER_ID appeas on multiple different times (LAT_UPDT_DT) as indicted below in red.

The aim is to calculate the timedifference between the fist time and the last time they appear.

What I have:

proc sort data = main_vic; by REFER_ID ;
run;

proc sql;
  create table want as
    select min(LST_UPDT_DT) as min_date format=datetime18.,
                 max(LST_UPDT_DT) as max_date format=datetime18.
             from main_vic;
quit;

This of course gives me only the first and the last date of all REFER_ID

How can I group REFER_ID so that I always receive the first and last visit of that REFER_ID?

I tried to add a by statement, but was not sucessful.

Any ideas are highly appreaciated.

Re: Group identical ids and indicate first and last date of an incident per id

Jagadishkatam — Fri, 09 Jun 2017 02:22:04 GMT

Please try the below code, it is untested but should work

also included the difference in days and hours

proc means data=have ;
class refer_id;
var lst_updt_dt;
output out=want min=min max=max;
run;

data want2;
set want;
diffinhours=(max-min)/3600;
diffindays=(max-min)/86400;
run;

Re: Group identical ids and indicate first and last date of an incident per id

kiranv_ — Fri, 09 Jun 2017 02:46:07 GMT

Something like this. I took a similar example instead of date time. I have used somenum

data abc;
input id someotherval $ somenum;
datalines;
1 a 20
1 b 30
1 c 40
2 a 40
2 a 50
2 a 45
;
run;

proc sql;
select a.*, max(somenum)-min(somenum) as diff from abc a
group by id;

;
quit;

Re: Group identical ids and indicate first and last date of an incident per id

Ksharp — Fri, 09 Jun 2017 13:22:20 GMT

data abc;
input id someotherval $ somenum;
datalines;
1 a 20
1 b 30
1 c 40
2 a 40
2 a 50
2 a 45
;
run;

proc sql;
select a.*, range(somenum) as diff from abc as a
group by id;
;
quit;

Re: Group identical ids and indicate first and last date of an incident per id

AnnaNZ — Mon, 12 Jun 2017 04:07:52 GMT

Great solution for this problem, thanks a lot !