Date:         Tue, 2 Nov 1999 13:49:31 -0800
Reply-To:     "William W. Viergever" <wwvierg@IBM.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "William W. Viergever" <wwvierg@IBM.NET>
Subject:      Mavens/Guru and Significant Others: HELP NEEDED
Content-Type: text/plain; charset="us-ascii"

Hi Folks:

Got a bugger of task that I thought might be amenable to someone's previous help that involved SQL (where I am a virgin). Checked out my folders and couldn't see anything that seemed geared to my problem.

Here goes:

I have a charge detail file consisting of inpatient cases (defined by: MED_REC=medical record number, ADM_DAT=admit date, and DIS_DAT=discharge date), where certain data is repeated (i.e., presumably what was *header* data), e.g., LOS (length of stay), TOT_CHG (total charges), and NUR_STA (nurse station - which gets mapped into a slightly more aggregated BED_TYP).

Again, this dataset has multiple (detail) obs per the case (i.e., per MED_REC ADM_DAT DIS_DAT) with a DET_DAT (detail charge date) and DET_CHG (the actual detail charge).

No problem, per se, here.

Client also has a "Transfers" file which has the same BY vars (i.e., per MED_REC ADM_DAT DIS_DAT) and a XFR_DAT (transfer date) and a PRE_STA and NEW_STA (i.e., the *previous* and the *new* nurse station also mapped into PRE_TYP and NEW_TYP, i.e., the bed types).

What I've been asked to do, is to distill down all the detail into a single record per stay, with the total charges and total length of stay (days) allocated into a series of day and charge "buckets", one per bed type (e.g., MS_DAY and MS_CHG for Med/Surg days and charges, NIC_DAY and NIC_CHG for neonatal intensive care days and charges, etc., etc.). Hence a person with a 7 day stay may have got admitted into the ICU/CCU, stayed there for 2 days where he/she stabilizes, then gets tranfered to a regular Med/Surg bed for 1 day where he/she takes a trun for the worse and goes back into ICU/CCU for the remaining 4 days (you get the idea -although this is an ugly example as folks usualy don't get discharged from ICU/CCU, unless they die, they usually go back to a Med/Surg or some other non-intensive setting).

The trouble is, of course, multiple transfers per stay.

Is there a snazzy SQL solution lurking in anyone's cerebral cortex?

Or is my only alternative to pass through the transfers file, figuring out maximal number of transfers across all cases, and then *build* a new transfers file which has a *single* OBS per case, with the entire vector of transfer dates and PRE and NEW bed types, for that case, encapsulated therein?

Brute force will always work for me, but "stealth and cunning" are far more fun!

TIA for any advice - it *will* be much appreciated; and as Ian, Pete, and Paul can vouch, I believe in "incentives"!

Later
----------------------------------------------------------------------------
William W. Viergever                      Voice  : (916) 483-8398
Viergever & Associates                    Fax    : (916) 486-1488
A SAS Institute Quality Partner (USA)     E-mail : wwvierg@ibm.net
Sacramento, CA 95825

"Hell hath no fury like a bureaucrat scorned."
- Milton Friedman

"Life is what happens while you're busy making other plans."
- John Lennon

"Against stupidity, the Gods themselves struggle in vain."
- Schiller
----------------------------------------------------------------------------