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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.