Previous Visits Volumes on Single Record?

[shellp55]

Hello

I am using SAS 9.3.  My database is where one line of data is one inpatient separation or ED visit.

I am trying to calculate something called the LACE score for each of my inpatient visits.  For all the criteria from within the current visit I'm fine (like LOS, certain diagnoses, etc.) BUT LACE also includes a value for "volumes of ED visits within the last 6 months".  This 6 months timeframe will be different depending on the admit date of the inpatient visit so I'm not clear on how I would calculate this?

Can someone give me something to start me off?  Thanks so very much.

[LinusH]

Some sample data could help...

[shellp55]

Hi

Okay the data below is a single patient as determined by the chart number.   The data elements are chart number, regno which is unique to the visit, type which is identifying visit type whether ED visit or inpatient, admit date and discharge date.

To restate my desired results, I want to have each inpatient visit identifying volumes of ED visits in the last 6 months compared to that inpatient visit.  Therefore, the data would look like this with the last column being the sum of ED visits within the last 6 months of the admission date for the inpatient visit:

A1234 005 IP 2012-07-04 2012-07-14  4

A1234 006 IP 2012-09-01 2012-09-07  4

A1234 007 IP 2012-12-24 2012-12-31  1

data test_grp;

input @1 chart $5.

   @6 regno  $3.

   @9 Type $2.

   @11 AdmDate yymmdd8.

   @19 DisDate yymmdd8.

;

format admdate yymmdd10. disdate yymmdd10.;

cards;

A1234001ED2012032920120329

A1234002ED2012042920120429

A1234003ED2012052920120529

A1234004ED2012062920120629

A1234005IP2012070420120714

A1234006IP2012090120120907

A1234007IP2012122420121231

run;

All assistance greatly appreciated.

[Patrick · Accepted solution]

What about:

proc sql;

  create table want as

    select a.*,count(*) as visit

    from test_grp a left join test_grp b

     on a.chart=b.chart

      and b.AdmDate < a.AdmDate < intnx('month',b.AdmDate,6,'s')

     where a.type='IP' and b.type='ED'

    group by 1,2,3,4,5

  ;

quit;

[shellp55]

Thanks Patrick!

For learning purposes, what is the group by doing i.e. do I need it?  I expect to have many, many inpatient visits so I'm not sure if I have to expand that group thus the query.

Thanks.

[Patrick]

The group by determines the group for which statistics like the count(*) function gets applied. So yes, you need it or the counts will be wrong.

[shellp55]

Okay thanks...but does it remain as 1, 2, 3 ,4, 5 no matter how many records I have in my database (the sample only had 1)?  I'm asking what it's based on, is it the number of variables in the sample?  So if I have more variables then I must add to this?  Sorry, just not understanding how to extrapolate beyond the one case in my sample data.

Thanks.

[Patrick]

Yes, the 1,2,3,4,5 stands for the variables and as I've used the select * it's the order in which they are stored in the table.

You can also write out the variable names in the group by statement which actually would be cleaner coding if using a select * (sorry, was just a bit lazy).

Think of the variables in the group by as what you would use in any by statement (eg. in a proc means). If you're having additional variables in your data source then it's up to you if this adds an additional aggregation level or not - and depending on this you need to add the variable to the group by statement or not.

[chuie]

Hi Patrik,

I have similar question posted today. will you please guide me.

Thank you