Date:         Wed, 23 Jun 2004 11:10:41 -0700
Reply-To:     "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Subject:      Re: PROC EXPAND / time series recs when dates are missing
Comments: To: Karen Olson <karen.olson@TCH.HARVARD.EDU>
Content-Type: text/plain

Hi Karen,



data sample;
    input hosp vdate mmddyy10. Frequency;
    format vdate mmddyy10.;
cards;
2 05/25/2004 167
2 05/26/2004  53
2 05/31/2004 243
2 06/01/2004 213
2 06/02/2004 229
;
run;


proc expand data=sample out=result from=day to=day method=none;
    id vdate;
    convert Frequency=Frequency / transformout=( setmiss 0.0 );
    format Frequency 7.3;
run;




Hope this is helpful,
Mark Terjeson
Reporting, Analysis, and Procurement Section
Information Services Division
Department of Social and Health Services
State of Washington
mailto:terjem@dshs.wa.gov






-----Original Message-----
From: Karen Olson [mailto:karen.olson@TCH.HARVARD.EDU]
Sent: Wednesday, June 23, 2004 10:54 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: PROC EXPAND / time series recs when dates are missing

I used proc freq to count records by date and saved the frequencies in a
dataset with an ODS OUTPUT statement.  Occasionally there are dates with
no events and so the output dataset does not create a record for that
date.  However, I want to do some time series analyses and need a record
for every date.  Therefore, I need to create records for the missing dates
where the frequency of the event = 0.

I thought maybe PROC EXPAND would do this.  I can get new records with the
missing dates, but the value of the frequency variable ends up being some
interpolated value.  Is there a way to use this procedure and get the
value to always be zero if the date had been missing in the first place?

For example, I start with this:

hosp        vdate    Frequency

   2    05/25/2004       167
   2    05/26/2004        53
   2    05/31/2004       243
   2    06/01/2004       213
   2    06/02/2004       229

And I want this:

hosp       vdate    Frequency

   2    25MAY2004     167.000
   2    26MAY2004      53.000
   2    27MAY2004       0.000
   2    28MAY2004       0.000
   2    29MAY2004       0.000
   2    30MAY2004       0.000
   2    31MAY2004     243.000
   2    01JUN2004     213.000
   2    02JUN2004     229.000

Thanks,

Karen Olson
Children's Hospital Boston
karen.olson@tch.harvard.edu