Date:         Sat, 15 Oct 2005 11:49:28 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Filling Time Gaps in Datasets with Holes

It may be possible to nudge the SQL optimizer into an efficient solution,
but in this case I would use DATA steps instead. Tested code:

   data all / view=all;
   set existing(keep = store item);
   by store item;
   if last.item;
   month = '15MAY2004'd;
   do until (month='15DEC2004'd);
      month = intnx('month',month,1,'same');
      format month date9.;
      output;
      end;
   run;

   data postprocessed;
   merge all existing;
   by store item month;
   run;

On Wed, 12 Oct 2005 18:11:13 -0700, Jon Freitag <jonfreitag@COMCAST.NET>
wrote:

>I have the following dataset consisting of two group variables (STORE
>and ITEM), a MONTH field, and a quantity.
>The EXISTING DATA have missing MONTHS that need to be added to the
>dataset by STORE and ITEM.
>
> Some STORE/ITEM combinations have MONTHS missing prior to the first
>transaction.
> Some STORE/ITEM combinations have MONTHS missing between actual
>transactions.
> Some STORE/ITEM combinations have MONTHS missing after the last
>transaction.
>
>The time period has a start MONTH = 15JUN2004 and an end MONTH =
>15DEC2004.
>
>The POST-PROCESSED DATA shows what the desired output should contain.
>
>I attempted this using PROC SQL joining the EXISTING DATA to a dataset
>containing all combinations of STORE\ITEM\MONTH.
>In large datasets, this method took an unacceptably long time to
>process.
>
>Is there an efficient way to accomplish this task?
>Thanks,
>Jon
>
>
>
>
>EXISTING DATA
>STORE   ITEM    MONTH           QTY
>A       Y       15JUN2004       1
>A       Y       15JUL2004       1
>A       Y       15NOV2004       1
>A       Z       15SEP2004       1
>A       Z       15DEC2004       1
>B       Y       15JUL2004       1
>B       Y       15AUG2004       1
>B       Y       15SEP2004       1
>B       Z       15AUG2004       1
>B       Z       15OCT2004       1
>
>POST-PROCESSED DATA
>STORE   ITEM    MONTH           QTY
>A       Y       15JUN2004       1
>A       Y       15JUL2004       1
>A       Y       15AUG2004       .       * ADDED
>A       Y       15SEP2004       .       * ADDED
>A       Y       15OCT2004       .       * ADDED
>A       Y       15NOV2004       1
>A       Y       15DEC2004       .       * ADDED
>A       Z       15JUN2004       .       * ADDED
>A       Z       15JUL2004       .       * ADDED
>A       Z       15AUG2004       .       * ADDED
>A       Z       15SEP2004       1
>A       Z       15OCT2004       .       * ADDED
>A       Z       15NOV2004       .       * ADDED
>A       Z       15DEC2004       1
>B       Y       15JUN2004       .       * ADDED
>B       Y       15JUL2004       1
>B       Y       15AUG2004       1
>B       Y       15SEP2004       1
>B       Y       15OCT2004       .       * ADDED
>B       Y       15NOV2004       .       * ADDED
>B       Y       15DEC2004       .       * ADDED
>B       Z       15JUN2004       .       * ADDED
>B       Z       15JUL2004       .       * ADDED
>B       Z       15AUG2004       1
>B       Z       15SEP2004       .       * ADDED
>B       Z       15OCT2004       1
>B       Z       15NOV2004       .       * ADDED
>B       Z       15DEC2004       .       * ADDED