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

[appleorange]

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.

[naveen_srini]

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

[Ksharp]

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

[Jagadishkatam · Accepted solution]

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

[shubhayog]

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;

[appleorange]

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!