Bill- Try this one on for size - it's sort of a mixture of SQL and brute force. There's also a data step, means and transpose thrown in. But, it works and the code is relatively compact. I made a few assumptions about your data which may or may not be correct but the code should be modifiable to meet your needs. Drop me a line or give me a call if you want any clarification.

There is no limitation on the number of bed types - you don't have to know them in advance as long as they're mapped consistently in both the "cases" and "transfers" files. I included my test data so you can give it a try.

===============================================================

data cases; input med_rec adm_dat dis_dat los tot_chg bed_typ $ det_dat det_chg; cards; 1 1 10 10 5400 ICU 1 1000 1 1 10 10 5400 ICU 2 2000 1 1 10 10 5400 MS 4 500 1 1 10 10 5400 ICU 6 1500 1 1 10 10 5400 MS 8 100 1 1 10 10 5400 MS 9 300 2 2 8 7 4500 NIC 3 2500 2 2 8 7 4500 NIC 4 500 2 2 8 7 4500 NIC 6 1000 2 2 8 7 4500 MS 8 500 3 2 5 4 1500 MS 2 650 3 2 5 4 1500 MS 3 850 ; run;

data xfers; input med_rec adm_dat dis_dat xfer_dat pre_typ $ new_typ $; cards; 1 1 10 3 ICU MS 1 1 10 6 MS ICU 1 1 10 8 ICU MS 2 2 8 7 NIC MS ; run;

data xferdet(keep=MED_REC ADM_DAT LOS BED_TYP); set xfers; by MED_REC ADM_DAT;

LastLOS = lag(XFER_DAT);

if first.ADM_DAT then do; LOS = (XFER_DAT - ADM_DAT); BED_TYP = PRE_TYP; end; else do; LOS = (XFER_DAT - LastLOS); BED_TYP = PRE_TYP; end; output;

if last.ADM_DAT then do; LOS = (DIS_DAT - XFER_DAT) + 1; BED_TYP = NEW_TYP; output; end; run;

proc means data=xferdet noprint nway; class MED_REC ADM_DAT BED_TYP; var LOS; output out=xfertot sum=BED_LOS; run;

proc sql; create table TOTS as select C.MED_REC, C.ADM_DAT, C.BED_TYP, case when BED_LOS eq . then LOS else BED_LOS end as BED_LOS, sum(DET_CHG) as Charges from CASES C left join XFERTOT X on C.MED_REC eq X.MED_REC and C.ADM_DAT eq X.ADM_DAT and C.BED_TYP eq x.BED_TYP group by C.MED_REC,C.ADM_DAT,C.BED_TYP,LOS,BED_LOS; quit;

proc transpose data=tots out=tot_los(drop=_NAME_) prefix=LOS_; by MED_REC ADM_DAT; id BED_TYP; var BED_LOS; run;

proc transpose data=tots out=tot_chg(drop=_NAME_) prefix=CHG_; by MED_REC ADM_DAT; id BED_TYP; var Charges; run;

data BIG_SET; merge tot_los tot_chg; by MED_REC ADM_DAT; run;

========================================================================

Pete Lund WA State Caseload Forecast Council (360) 902-0086 voice (360) 902-0084 fax peter.lund@cfc.wa.gov

-----Original Message----- From: William W. Viergever [mailto:wwvierg@IBM.NET] Sent: Tuesday, November 02, 1999 1:50 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Mavens/Guru and Significant Others: HELP NEEDED

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