topic Re: how to create subset of two dates that occur before a reference date in SAS Data Management

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

lillymaginta — Sat, 06 Feb 2016 14:12:45 GMT

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)

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

Jagadishkatam — Sat, 06 Feb 2016 14:57:37 GMT

Could you please also provide the sample output for better understanding

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

lillymaginta — Sat, 06 Feb 2016 15:26:12 GMT

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!

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

Jagadishkatam — Sun, 07 Feb 2016 02:38:49 GMT

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;

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

Patrick — Sun, 07 Feb 2016 07:02:06 GMT

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.

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

Jagadishkatam — Sun, 07 Feb 2016 14:36:24 GMT

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 .

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

lillymaginta — Sun, 07 Feb 2016 14:59:09 GMT

Jag,

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

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

lillymaginta — Sun, 07 Feb 2016 15:11:47 GMT

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

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

Reeza — Sun, 07 Feb 2016 21:30:51 GMT

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.

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

lillymaginta — Sun, 07 Feb 2016 21:48:18 GMT

Thank you for the suggestion.

lilly

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

lillymaginta — Sat, 13 Feb 2016 12:06:20 GMT

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.

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

Jagadishkatam — Sun, 14 Feb 2016 16:31:59 GMT

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