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.
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
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
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
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
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;
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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.