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

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

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

Hi

 

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. 

 

Capture.PNG

 

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. 

 

 


Accepted Solutions
Solution
‎06-12-2017 12:06 AM
Super User
Posts: 1,115

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

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;
Thanks,
Jag

View solution in original post


All Replies
Solution
‎06-12-2017 12:06 AM
Super User
Posts: 1,115

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

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;
Thanks,
Jag
Contributor
Posts: 58

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

Great solution for this problem, thanks a lot !
Regular Contributor
Posts: 230

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

[ Edited ]

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;

 

Grand Advisor
Posts: 9,582

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

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 155 views
  • 3 likes
  • 4 in conversation