BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lillymaginta
Lapis Lazuli | Level 10
id     d_date        rx_name       rx_date                   h_date
1      3/2/2005          a                    3/4/2005                 2/29/2005
1                        b                    4/4/2005
1                        c                    2/25/2005
1                        d 
1                        e
1
1
1
1
2
2
2
2
3
3
3

I have the following data where h_date is hospitalization date and d_date is discharge date. Each rx_name happen in the corresponding rx_Date. I am trying to find rx_name that happened within 30 day before h_date. Also, I want seperately to find rx_name that happaned within 30 days after d_date. 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Your requirements aren't consistent between your first post, last post and your output. 

That being said, this is how I would do it. First I'd create separate tables for my discharge and hospitalization data.  Then I'd use SQL to do the join conditons. This method is more effectie when you have multiple hospitalizations and discharge dates per ID. You haven't shown that, but that's the usual structure for this type of data. I'll leave the data separation as an exercise for you.

 

The sample code below finds a drugs with a date 30 days before hospitalization, in a table called PRE30

and any drugs 30 days after discharge, called POST30. You may want to verify the conditons are what you want, I didn't understand your requirements. You can modify the WHERE condition to match your needs, and I would consider changing the LT and GT to LE and GE, but again, that depends on your requirements. 

 

data hosp;
informat h_date mmddyy10.;
format h_date date9.;
input id $ h_date;
cards;
1 2/28/2005
;
run;

data disch;
informat d_date mmddyy10.;
format d_date date9.;
input id $ d_date;
cards;
1 3/2/2005
;
run;

data drugs;
informat rx_date mmddyy10.;
format rx_date date9.;
input id $ rx $ rx_date;
cards;
1 A 3/4/2005
1 B 4/4/2005
1 D 2/25/2005
;
run;

proc sql;
create table pre30 as
select a.Id, b.rx, b.rx_date, a.h_date, a.h_date-b.rx_date as days_before_hosp
from hosp as a
join drugs as b
on a.id=b.id
where 0< (a.h_date-b.rx_date) < 30;
quit;

proc sql;
create table post30 as
select a.Id, b.rx, b.rx_date, a.d_date, b.rx_date-a.d_date as days_after_disch
from disch as a
join drugs as b
on a.id=b.id
where 0< (b.rx_date-a.d_date) < 30;
quit;

View solution in original post

10 REPLIES 10
Reeza
Super User

Please post a sample of your desired output. 

lillymaginta
Lapis Lazuli | Level 10

I want two seperate datasets;

dataset A, rx_name within 30 days before d_date

 

id  rx_name

1   c

 

dataset b, rx_name within 30 days post :

 

id rx_name

1   a

Reeza
Super User

Your requirements aren't consistent between your first post, last post and your output. 

That being said, this is how I would do it. First I'd create separate tables for my discharge and hospitalization data.  Then I'd use SQL to do the join conditons. This method is more effectie when you have multiple hospitalizations and discharge dates per ID. You haven't shown that, but that's the usual structure for this type of data. I'll leave the data separation as an exercise for you.

 

The sample code below finds a drugs with a date 30 days before hospitalization, in a table called PRE30

and any drugs 30 days after discharge, called POST30. You may want to verify the conditons are what you want, I didn't understand your requirements. You can modify the WHERE condition to match your needs, and I would consider changing the LT and GT to LE and GE, but again, that depends on your requirements. 

 

data hosp;
informat h_date mmddyy10.;
format h_date date9.;
input id $ h_date;
cards;
1 2/28/2005
;
run;

data disch;
informat d_date mmddyy10.;
format d_date date9.;
input id $ d_date;
cards;
1 3/2/2005
;
run;

data drugs;
informat rx_date mmddyy10.;
format rx_date date9.;
input id $ rx $ rx_date;
cards;
1 A 3/4/2005
1 B 4/4/2005
1 D 2/25/2005
;
run;

proc sql;
create table pre30 as
select a.Id, b.rx, b.rx_date, a.h_date, a.h_date-b.rx_date as days_before_hosp
from hosp as a
join drugs as b
on a.id=b.id
where 0< (a.h_date-b.rx_date) < 30;
quit;

proc sql;
create table post30 as
select a.Id, b.rx, b.rx_date, a.d_date, b.rx_date-a.d_date as days_after_disch
from disch as a
join drugs as b
on a.id=b.id
where 0< (b.rx_date-a.d_date) < 30;
quit;
lillymaginta
Lapis Lazuli | Level 10

Thanks Reeza, this is exactly what I wanted which is rx during the 30 days pre and rx within the 30 days post. I tried it and it works perfect! Lilly 

lillymaginta
Lapis Lazuli | Level 10

One issue with the code, it produces duplicates of the same id-rx combination for the same rx_date;

id  rx_name   rx_Date

1   a               05/01/2004

1   a               05/01/2004

1   a               05/01/2004

 

I tried to proc sort nodupkey by id, rx_name after the code you provided but is there a way to avoid this from the first place? thanks! 

lillymaginta
Lapis Lazuli | Level 10

The first part of the code works but the second part does not work, return 0 observations: 

proc sql;
create table post30 as
select a.Id, b.rx, b.rx_date, a.d_date, b.rx_date-a.d_date as days_after_disch
from disch as a
join drugs as b
on a.id=b.id
where 0< (b.rx_date-a.d_date) < 30;
quit;
FreelanceReinh
Jade | Level 19

@lillymaginta wrote:

One issue with the code, it produces duplicates of the same id-rx combination for the same rx_date; 


 

Hi @lillymaginta,

 

I think, such duplicates can occur if the drug intake happened within the (overlapping) 30-days time windows of two or more hospitalizations. Are the three corresponding H_DATE (or D_DATE) values in your example all the same when you run Reeza's code? Probably not.

 

If you don't select H_DATE, D_DATE etc. and you don't need the duplicates, you could use the keyword DISTINCT:

select distinct a.Id, b.rx, b.rx_date

 

lillymaginta
Lapis Lazuli | Level 10

Thank you for the suggesion Freelance

Reeza
Super User
Post better sample data - make sure it replicates the issue and I can take a look. Given the sample data the code works.
lillymaginta
Lapis Lazuli | Level 10

I just relaized the data was truncated post h_date, the second code worked. Thank you

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3352 views
  • 2 likes
  • 3 in conversation