topic Re: How to check if the first and last date of ids are the same? in SAS Procedures

How to check if the first and last date of ids are the same?

appleorange — Fri, 27 Mar 2015 17:08:57 GMT

If I have a dataset as such:

Pat_ID Service_Date ID

A 1/2/2012 11

A 1/2/2012 12

B 4/22/2012 11

B 4/25/2012 11

C 3/14/2012 10

C 3/14/2012 11

C 3/14/2012 9

D 3/1/2012 9

D 3/5/2012 12

....

I need to split this dataset such that one section will have the Pat_IDs with the same service dates and the rest with different service dates. The best way I could think of doing this was to sort the service dates and check if the first and last one within a Pat_ID is the same. I am not quite sure how to do that since if I use proc sort by service_date, it will take out the service_date if it's the same as the other ones in the Pat_ID. I'm also not sure if first.service_date and last.service_date is the best way to do this.

Re: How to check if the first and last date of ids are the same?

naveen_srini — Sat, 28 Mar 2015 12:27:55 GMT

I think your approach is right. You could do something like:

proc sort data=input_dataset out=splitone nouniquekey;

by service_date;

run;

proc sort data=input_dataset out=splittwo nodupkey;

by service_date;

run;

Please check the sort procedure documentation

Re: How to check if the first and last date of ids are the same?

Ksharp — Sat, 28 Mar 2015 13:50:12 GMT

How to split your table ? like this :

Table1

A 1/2/2012 11

A 1/2/2012 12

Table2

B 4/22/2012 11

Table3

B 4/25/2012 11

Re: How to check if the first and last date of ids are the same?

Jagadishkatam — Sat, 28 Mar 2015 14:29:51 GMT

data have;

input Pat_ID$ Service_Date: mmddyy10. ID;

format Service_Date date9.;

cards;

A 1/2/2012 11

A 1/2/2012 12

B 4/22/2012 11

B 4/25/2012 11

C 3/14/2012 10

C 3/14/2012 11

C 3/14/2012 9

D 3/1/2012 9

D 3/5/2012 12

;

proc sort data=have;

by pat_id service_Date;

run;

data want;

set have;

retain new_date ;

by pat_id service_Date;

if first.pat_id then new_date= Service_Date;

if last.pat_id and new_date= Service_Date then flag=1;

format Service_Date new_date date9.;

run;

proc sort data=want;;

by pat_id descending flag;

run;

data want2 want3;

set want;

by pat_id descending flag ;

retain flag2;

if first.pat_id then flag2=flag;

if flag2=1 then output want2;

else output want3;

run;

Thanks,

Jag

Re: How to check if the first and last date of ids are the same?

shubhayog — Sat, 28 Mar 2015 17:14:45 GMT

Hi,

I have updated your data as follows for checking multiple conditions, I hope the following code will help you,

data have;

input Pat_ID$ Service_Date mmddyy10. ID;

format Service_Date mmddyy10.;

datalines;

A 1/2/2012 11

A 1/2/2012 12

A 2/21/2012 9

B 4/22/2012 11

B 4/25/2012 11

C 2/21/2012 11

C 3/14/2012 10

C 3/14/2012 11

C 3/14/2012 9

D 3/1/2012 9

D 3/5/2012 12

;

run;

proc sort

data=have

out=want1 nouniquekey;

by Pat_ID Service_Date;

run;

proc sort

data=have;

by Pat_ID Service_Date;

run;

data want2;

set have;

by Pat_ID Service_Date;

if first.Service_Date and last.Service_Date;

run;

Re: How to check if the first and last date of ids are the same?

appleorange — Wed, 01 Apr 2015 19:58:38 GMT

I apologize for responding so late, I was not able to come back to this until today. Jag had the right idea, I needed to check if the first and last service dates for the pat_ids were the same and if so output them to one data set. Thanks a lot!