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?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.