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: 128

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,137

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

Posted in reply to lillymaginta
Could you please also provide the sample output for better understanding
Thanks,
Jag
Frequent Contributor
Posts: 128

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

Posted in reply to Jagadishkatam

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,137

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

Posted in reply to lillymaginta

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: 4,173

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

Posted in reply to lillymaginta

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,137

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: 128

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

Posted in reply to Jagadishkatam

Jag,

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

Frequent Contributor
Posts: 128

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

Posted in reply to lillymaginta

  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: 19,850

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

Posted in reply to lillymaginta

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: 128

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

Thank you for the suggestion. 

lilly 

Frequent Contributor
Posts: 128

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

Posted in reply to lillymaginta

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,137

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

Posted in reply to lillymaginta
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
  • 586 views
  • 4 likes
  • 4 in conversation