BookmarkSubscribeRSS Feed
lillymaginta
Lapis Lazuli | Level 10
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! 

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

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;
Ksharp
Super User

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;
PeterClemmensen
Tourmaline | Level 20

@lillymaginta Did this solve your problem? 🙂

lillymaginta
Lapis Lazuli | Level 10

both produces an error. 

PeterClemmensen
Tourmaline | Level 20

What error are you getting?

PeterClemmensen
Tourmaline | Level 20

This is my log running my solution

 

LOGLOG.PNG

lillymaginta
Lapis Lazuli | Level 10

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. 

PeterClemmensen
Tourmaline | Level 20

Do you have any IDs for which there are more than two records?

lillymaginta
Lapis Lazuli | Level 10

no each id would have one period for drug a and another period for drug b. So only two per each. 

PeterClemmensen
Tourmaline | Level 20

What error are you getting?

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1331 views
  • 0 likes
  • 3 in conversation