Date:         Wed, 27 Aug 2003 14:30:09 -0400
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Re: How to merge observations?

I would probably do it in two steps, one to arrnage the RET series into
single observations and then one to combine that with the June data:

   proc transpose data=somedata out=ret prefix=ret;
   by permno;
   var ret;
   run;

   data result;
   merge somedata(where = (month=6) drop=ret)
         ret(drop = _name_);
   by permno;
     run;

But it can be done in a single MERGE:

   data result;
   merge somedata (where = (month=6) drop=ret)
         somedata (keep = permno month ret rename = (month=retindex) );
   by permno;
   array retarray(*) ret1-ret12;
   retain            ret1-ret12;
   if first.permno then do i = 1 to 12; retarray(i) = .; end;
   retarray(retindex) = ret;
   if last.permno;
   drop retindex ret i;
   run;

On Wed, 27 Aug 2003 08:38:49 -0700, Hill Smith <ldh3@SINA.COM> wrote:

>I upload some data. Thank you.
>
>permno  Year    Month   date    ret     Prc     MEflag
>10006   1964    8       1964-08-31      0.01278 78.625  8
>10006   1964    9       1964-09-30      0.074722        84.5    8
>10006   1964    10      1964-10-30      0.029586        87      8
>10006   1964    11      1964-11-30      -0.023276       84.25   8
>10006   1964    12      1964-12-31      -0.05638        79.5    8
>10006   1965    1       1965-01-29      0.053459        83.75   8
>10006   1965    2       1965-02-26      -0.02597        80.75   8
>10006   1965    3       1965-03-31      0.040248        84      8
>10006   1965    4       1965-04-30      0.084821        91.125  8
>10006   1965    5       1965-05-28      -0.043621       86.25   8
>10006   1965    6       1965-06-30      -0.104348       77.25   8
>10006   1965    7       1965-07-30      0.009709        78      8
>10014   1964    8       1964-08-31      -0.090909       2.5     1
>10014   1964    9       1964-09-30      0.05    2.625   1
>10014   1964    10      1964-10-30      0.047619        2.75    1
>10014   1964    11      1964-11-30      -0.090909       2.5     1
>10014   1964    12      1964-12-31      0       2.5     1
>10014   1965    1       1965-01-29      0.05    2.625   1
>10014   1965    2       1965-02-26      0.047619        2.75    1
>10014   1965    3       1965-03-31      0       2.75    1
>10014   1965    4       1965-04-30      0.045455        2.875   1
>10014   1965    5       1965-05-28      -0.086957       2.625   1
>10014   1965    6       1965-06-30      -0.047619       2.5     1
>10014   1965    7       1965-07-30      0.1     2.75    1
>10030   1964    8       1964-08-31      -0.02521        58      6
>10030   1964    9       1964-09-30      0.018966        58.5    6
>10030   1964    10      1964-10-30      0.025641        60      6
>10030   1964    11      1964-11-30      -0.00625        59.625  6
>10030   1964    12      1964-12-31      0.007547        59.375  6
>10030   1965    1       1965-01-29      0.048421        62.25   6
>10030   1965    2       1965-02-26      0.048193        65.25   6
>10030   1965    3       1965-03-31      -0.006513       -64.125 6
>10030   1965    4       1965-04-30      0.044834        67      6
>10030   1965    5       1965-05-28      -0.029851       65      6
>10030   1965    6       1965-06-30      -0.081538       59      6
>10030   1965    7       1965-07-30      0.033898        61      6