Date:         Thu, 9 Jul 2009 22:42:52 -0400
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: Inserting dates into a data set
Comments: To: SUBSCRIBE SAS-L Anonymous <randistan69@HOTMAIL.COM>

You can get the unique combination of id1,id2,id3 from original data,
then JOIN the date list you have, so that each combination of id1,2,3
will get the full list of date, this will be the shell dataset, then
use this shell dataset to merge with the original data:

data xx;
input Date $  ID1  ID2 $ ID3 $ VarA;
cards;
Jul1     1       AA     X1     15.3
Jul3     1       AA     X1     18.4
Jul5     1       AA     X1     14.2
Jul2     1       AB     X1     17.4
Jul2     1       AB     X2     18.8
Jul4     1       AB     X2     19.2
Jul1     2       AA     X1     18.3
Jul5     2       AA     X1     18.9
Jul3     2       AB     X3     18.3
;

data datlst;
input date $;
cards;
Jul1
Jul2
Jul3
Jul4
Jul5
;

proc sql;
create table shell as
select distinct id1,id2,id3,b.date
from xx, datlst b
order by id1,id2,id3,date
;

proc sort data=xx;
by id1 id2 id3 date;
run;

data final;
 merge xx shell;
by id1 id2 id3 date;
run;

proc print;
run;

   Date    ID1    ID2    ID3    VarA

   Jul1     1     AA     X1     15.3
   Jul2     1     AA     X1       .
   Jul3     1     AA     X1     18.4
   Jul4     1     AA     X1       .
   Jul5     1     AA     X1     14.2
   Jul1     1     AB     X1       .
   Jul2     1     AB     X1     17.4
   Jul3     1     AB     X1       .
   Jul4     1     AB     X1       .
   Jul5     1     AB     X1       .
   Jul1     1     AB     X2       .
   Jul2     1     AB     X2     18.8
   Jul3     1     AB     X2       .
   Jul4     1     AB     X2     19.2
   Jul5     1     AB     X2       .
   Jul1     2     AA     X1     18.3
   Jul2     2     AA     X1       .
   Jul3     2     AA     X1       .
   Jul4     2     AA     X1       .
   Jul5     2     AA     X1     18.9
   Jul1     2     AB     X3       .
   Jul2     2     AB     X3       .
   Jul3     2     AB     X3     18.3
   Jul4     2     AB     X3       .
   Jul5     2     AB     X3       .


On Thu, 9 Jul 2009 19:47:21 -0400, Randy <randistan69@HOTMAIL.COM> wrote:

>Dear All:
>  My data set is as follows (where ID2 and ID3 are considered together as
>one identifier):
>
>Date    ID1     ID2     ID3   VarA
>Jul1     1       AA     X1     15.3
>Jul3     1       AA     X1     18.4
>Jul5     1       AA     X1     14.2
>Jul2     1       AB     X1     17.4
>Jul2     1       AB     X2     18.8
>Jul4     1       AB     X2     19.2
>Jul1     2       AA     X1     18.3
>Jul5     2       AA     X1     18.9
>Jul3     2       AB     X3     18.3
>
>I want to add the missing dates (I have a list of dates that excludes
>holidays) to each ID2 and ID3.  So my expanded data set should look like
this
>Date    ID1     ID2     ID3   VarA
>Jul1     1       AA     X1     15.3
>Jul2     1       AA     X1     .
>Jul3     1       AA     X1     18.4
>Jul4     1       AA     X1     .
>Jul5     1       AA     X1     14.2
>Jul1     1       AB     X1     .
>Jul2     1       AB     X1     17.4
>Jul3     1       AB     X1     .
>Jul4     1       AB     X1     .
>Jul5     1       AB     X1     .
>Jul1     1       AB     X2     .
>Jul2     1       AB     X2     18.8
>Jul3     1       AB     X2     .
>Jul4     1       AB     X2     19.2
>Jul5     1       AB     X2     .
>Jul1     2       AA     X1     18.3
>Jul2     2       AA     X1     .
>Jul3     2       AA     X1     .
>Jul4     2       AA     X1     .
>Jul5     2       AA     X1     18.9
>Jul1     2       AB     X3     .
>Jul2     2       AB     X3     .
>Jul3     2       AB     X3     18.3
>Jul4     2       AB     X3     .
>Jul5     2       AB     X3     .
>
>I would appreciate all your help.
>  Randy