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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.