SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how to create subset of two dates that occur before a reference date

Reply
Frequent Contributor
Posts: 110

how to create subset of two dates that occur before a reference date

Hi All,

I am a researcher who is new to dealing with date in large datasets. I have been trying to code this over last week but the process became complicated. Here is an example of my database:

gh.png

The date_ot is the date the ICD-9 was coded. And date_h= hospital admission date. I am trying to code the following: include people who have two ICD-9 codes of "200" that occur before date of hospitalization "date_h". Any help is apprecaited. Thank you (using SAS 9.4)

 

Trusted Advisor
Posts: 1,128

Re: how to create subset of two dates that occur before a reference date

Could you please also provide the sample output for better understanding
Thanks,
Jag
Frequent Contributor
Posts: 110

Re: how to create subset of two dates that occur before a reference date

Hi Jag,

Thank you for the reply. I adjusted the above table to make it easy to understand what I am trying to do:

h1.png

 

I want an output table that looks like this:

 

gh6.png

patient 1 and 3 were excluded because while they had 2 ICD-9 code of "200" they were reported after date_h

patient 2 was excluded because they had 1 ICD-9 code of 200

Only patient 4 was included because the 2 ICD-9 code of 2-- were reported before date_h

Thank you! 

Trusted Advisor
Posts: 1,128

Re: how to create subset of two dates that occur before a reference date

Thank you for your reply. Please try the below code

 

proc sql;
create table test as select id,count(icd_9), icd_9, date_ot,date_h from have where date_ot<date_h
 group by id,icd_9 having count(icd_9)<=2 ;
quit;
Thanks,
Jag
Respected Advisor
Posts: 3,887

Re: how to create subset of two dates that occur before a reference date

In your data could there be more than one hospitalization for a patient? And if yes how should the result look like for such a case?

 

What would help is if you could provide sample data as realistic as possible and not as a screen-shot but as data which we can directly process in SAS (eg. a data step creating such data).

That would help us to provide answers which are tested; and Jag might have realized what's not working in his SQL before posting it as an answer.

 

 

Trusted Advisor
Posts: 1,128

Re: how to create subset of two dates that occur before a reference date

Hi @Patrick thanks for your response. I would like to correct myself with regard to the code i posted. Could you please help me with this.

 

I got the output as expected with the code i posted. 

 

Here is the data I used 

 

id icd_9 date_ot date_h
1 200 7/1/2005 1/1/2005
1 200 2/3/2005 .
1 200 3/3/2004 .
1 201 4/3/2004 .
2 200 5/7/2005 2/1/2005
2 201 5/8/2005 .
3 200 5/9/2005 2/2/2005
3 201 6/4/2004 .
3 200 7/9/2005 .
4 200 5/4/2004 1/1/2005
4 205 5/3/2004 .
4 200 5/2/2004 .

 

Thanks,
Jag
Frequent Contributor
Posts: 110

Re: how to create subset of two dates that occur before a reference date

Jag,

Thank you for taking the time to respond. The code works, perfect! 

Frequent Contributor
Posts: 110

Re: how to create subset of two dates that occur before a reference date

  I am also trying to find id with 12 months of continous enrollment (no gap) before and after date_h. Each id can have multiple enrollment period (several start_e and end_e dates). In this example only id 1 should be included (have 12 months before and 12 months after).

id 2 will be excluded because there was a gap in coverage between 8/8/2006 and 1/1/2007

id 3 will be excluded because there was 12 months of continous enrollment before but not after date_h.

 

 

 

id start_e end_e     date_h
1 1/1/2005 1/1/2006  2/8/2008
1 2/3/2006 4/5/2013
2 5/7/2005 8/8/2006  4/5/2007
2 1/1/2007 2/2/2012
3 5/9/2005 5/9/2007  1/1/2007
3 6/4/2008 7/7/2012 

 

 

Super User
Posts: 17,784

Re: how to create subset of two dates that occur before a reference date

Can you please post your question as a new question/thread? Many users won't look at a question that's been marked answered and it helps others later on when searching for similar answers.

Frequent Contributor
Posts: 110

Re: how to create subset of two dates that occur before a reference date

Thank you for the suggestion. 

lilly 

Frequent Contributor
Posts: 110

Re: how to create subset of two dates that occur before a reference date

Hi Jag,

the code did not work on the original dataset. You specified count assuming I have the same ICD-9 code but I can have different one not necessarly the one I want. 

Trusted Advisor
Posts: 1,128

Re: how to create subset of two dates that occur before a reference date

hope you tried to change the variable name. could you please let me know the sample data how it actually looks now.
Thanks,
Jag
Ask a Question
Discussion stats
  • 11 replies
  • 572 views
  • 4 likes
  • 4 in conversation