Date:         Mon, 21 Jan 2002 11:49:46 -0800
Reply-To:     Cassell.David@EPAMAIL.EPA.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "David L. Cassell" <Cassell.David@EPAMAIL.EPA.GOV>
Subject:      Re: collapsing records and indexing
Comments: To: Dave Meyer <dmeyer@HOAGHOSPITAL.ORG>
Content-type: text/plain; charset=us-ascii

Dave Meyer <dmeyer@HOAGHOSPITAL.ORG> wrote [in part]:
> 1) My boss (an MD) was asking me if SAS had indexing capability like
> Access does (he is interested in run times for some of the ouput I
have
> been getting him - they are big files and it sometimes takes me
multiple
> hours to complete the run)

Yes, SAS has indexing.  Look at PROC DATASETS.  Or look at my SUGI 26
paper on sorting vs. indexing [it's on the SAS website].

If you're needing multiple hours to complete a single run, then either
you have files with millions and millions of records [all made of the
cosmic starSTUFF  :-] or you're doing your processing in a less-than-
optimal manner.  Maybe you should write back to the list and discuss
this.  There are many experts here who may be able to help you out.
And do NOT let your boss talk you into switching to MS Access!

> 2) I have just hit what is my trickiest problem to far with SAS - I
need
> have to collapse line item (record level) transaction items and return
> the member specific earliest "start date" and latest "end date" along
> with the detail that is identical from line to line -
> . . .
> So really John Q was in the hospital from 15278 to 15284 (6 days) and
> had 3 doc's billing for different services over the time - - so I am
> trying to be able to return the following:
>
> MEMBER         START DATE      END DATE      DOB
> John Q         15278           15284         -3287

You have already seen a PROC SQL approach.  You can do this with
PROC SUMMARY if you want [the next bit assumes you already have
the data set sorted or indexed on member:

proc summary data=YourDataSetHere;
  var start_date end_date;
  by member;
  id dob;
  output out=YourOutput
         min(start_date)=start_date  max(end_date)=end_date;
  run;

HTH,
David
--
David Cassell, CSC
Cassell.David@epa.gov
Senior computing specialist
mathematical statistician