id start date drug end_Date
1 01/01/2005 a 04/01/2005
1 02/01/2005 b 03/04/2005
2 02/03/2005 a 03/04/2005
2 01/02/2004 b 02/02/2004
I have the following database with multiple ids and only two drugs a and b. I want to create a database for drug a but to change the end date based on the start of drug. Here are my assumptions:
For patients 1, the start date is the same but the end date for drug a would be the start of drug b.
For patient 2, do not include because drug b started before a
each drug has one start and one end date
My output would look like this
id start date drug end_Date
1 01/01/2005 a 02/01/2005
Thanks!
Something like this?
data have;
format id start_date drug end_Date;
informat start_date end_date ddmmyy10.;
input
id $ start_date drug $ end_Date;
format start_date end_date ddmmyy10.;
datalines;
1 01/01/2005 a 04/01/2005
1 02/01/2005 b 03/04/2005
2 02/03/2005 a 03/04/2005
2 01/02/2004 b 02/02/2004
;
proc sort data = have;
by descending id descending drug;
run;
data want;
set have;
if start_date < lag(start_date) then do;
end_date = lag(start_date);
if drug='a' then output;
end;
run;
Assuming there are only two obs for each id.
data have;
format id start_date drug end_Date;
informat start_date end_date ddmmyy10.;
input
id $ start_date drug $ end_Date;
format start_date end_date ddmmyy10.;
datalines;
1 01/01/2005 a 04/01/2005
1 02/01/2005 b 03/04/2005
2 02/03/2005 a 03/04/2005
2 01/02/2004 b 02/02/2004
;
run;
data want;
merge have have(keep=id start_date rename=(id=_id start_date=_date) firstobs=2);
if id=_id and start_date lt _date then do;
end_date=_date;output;
end;
drop _:;
run;
@lillymaginta Did this solve your problem? 🙂
both produces an error.
What error are you getting?
in the original dataset that is exactly similar to the one above but with multiple ids, the code create missing data for some of the dates.
Do you have any IDs for which there are more than two records?
no each id would have one period for drug a and another period for drug b. So only two per each.
What error are you getting?
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.