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

[lillymaginta]

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:

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)

[Jagadishkatam]

Could you please also provide the sample output for better understanding

[lillymaginta]

Hi Jag,

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

I want an output table that looks like this:

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! 

[Jagadishkatam]

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;

[Patrick]

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.

[Jagadishkatam]

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 .

[lillymaginta]

Jag,

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

[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 

 

[Reeza]

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.

[lillymaginta]

Thank you for the suggestion. 

lilly 

[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. 

[Jagadishkatam]

hope you tried to change the variable name. could you please let me know the sample data how it actually looks now.