## Previous Visits Volumes on Single Record?

Solved
Regular Contributor
Posts: 177

# Previous Visits Volumes on Single Record?

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.

Accepted Solutions
Solution
‎11-19-2013 05:00 PM
Posts: 4,736

## Re: Previous Visits Volumes on Single Record?

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

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

group by 1,2,3,4,5

;

quit;

All Replies
Super User
Posts: 5,876

## Re: Previous Visits Volumes on Single Record?

Some sample data could help...

Data never sleeps
Regular Contributor
Posts: 177

## Re: Previous Visits Volumes on Single Record?

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.

@19 DisDate yymmdd8.

;

cards;

A1234001ED2012032920120329

A1234002ED2012042920120429

A1234003ED2012052920120529

A1234004ED2012062920120629

A1234005IP2012070420120714

A1234006IP2012090120120907

A1234007IP2012122420121231

run;

All assistance greatly appreciated.

Solution
‎11-19-2013 05:00 PM
Posts: 4,736

## Re: Previous Visits Volumes on Single Record?

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

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

group by 1,2,3,4,5

;

quit;

Regular Contributor
Posts: 177

## Re: Previous Visits Volumes on Single Record?

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.

Posts: 4,736

## Re: Previous Visits Volumes on Single Record?

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.

Regular Contributor
Posts: 177

## Re: Previous Visits Volumes on Single Record?

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.

Posts: 4,736

## Re: Previous Visits Volumes on Single Record?

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.

Contributor
Posts: 32

## Re: Previous Visits Volumes on Single Record?

Hi Patrik,

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

Thank you

☑ This topic is solved.