Date:         Tue, 7 Oct 2008 20:42:14 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Subject:      Re: DATA INPUT QUESTIONS

On Mon, 6 Oct 2008 12:53:13 -0500, ./ ADD NAME=Data _null_,
<iebupdte@GMAIL.COM> wrote:

>To me you need to "frame" each date with 5 values of type.  Your need
>data has TYPE missing for the manufactured observations but that can
>be accomodated also.  For a problem like this I like to use the
>proloadfmt feature of PROC SUMMARY, for example.
>
>proc format;
>   value type 1=1 2=2 3=3 4=4 5=5;
>   select type;
>   run;
>data work.have;
>   input Date:mmddyy. Type balance;
>   format data mmddyy.;
>   cards;
>01/15/2004 1 1100.00
>01/15/2004 2 398.95
>01/15/2004 4 90358.25
>02/14/2004 3 336.47
>;;;;
>   run;
>proc summary data=have nway completetypes;
>   class date;
>   class type / preloadfmt;
>   format type type.;
>   output out=need(drop=_:) idgroup(out(balance)=);
>   run;
>proc print;
>   run;
>data needMissingType; /* ??? don't understand why you want TYPE missing */
>   set need;
>   if missing(balance) then call missing(type);
>   run;
>proc print;
>   run;
>
>
>
>
>On 10/6/08, kwu0914 <kwu0914@gmail.com> wrote:
>> Hello all,
>>
>> I had following data and want to read into a SAS data set named A:
>>
>> Date Type balance
>> 01/15/2004 1 1100.00
>> 01/15/2004 2 398.95
>> 01/15/2004 4 90358.25
>> 02/14/2004 3 336.47
>>
>> A should look like this after creating:
>> 01/15/2004 1 1100.00
>> 01/15/2004 2 398.95
>> 01/15/2004 .  .
>> 01/15/2004 4 90358.25
>> 01/15/2004 .  .
>> 02/14/2004 .  .
>> 02/14/2004 .  .
>> 02/14/2004 3 336.47
>> 02/14/2004 .  .
>> 02/14/2004 .  .
>>
>> The request is simple:there has to be 5 records associated with each unique
>> date. For example, for 01/15/2004, there are
>> type 1, 2, 4 for 3 records, but missing 3 and 5, so need to add 2 records
>> with date 01/15/2004, and missing values for
>> type and balance, the data in result data set A has to match request
>> exactly.
>>
>> You can assume that input data has been sorted on date and type;
>>
>> I had scrach my head over 3 hrs on it and did not get it right. Thanks for
>> your comments in advance!
>>

A similar approach, but done with SQL:

   proc sql;

   create table one5 ( One5 num );
   insert into  one5 ( One5 )
    values(1)
    values(2)
    values(3)
    values(4)
    values(5);

   create table need as
    select Date2 as Date, Type, Balance
     from one5
          cross join
          (select distinct Date as Date2 from have)
          left join have
           on One5 = Type and Date = Date2
    order by Date2, One5;

   drop table one5;

   quit;

Result:

     Date      Type     Balance

   01/15/04      1      1100.00
   01/15/04      2       398.95
   01/15/04      .          .
   01/15/04      4     90358.25
   01/15/04      .          .
   02/14/04      .          .
   02/14/04      .          .
   02/14/04      3       336.47
   02/14/04      .          .
   02/14/04      .          .