Hi all,   I have data (Rsquare2) here and want to calculate r2_avg over past 12 months except the most recent month (t-12 to t-2). Then I tried to use your code but the result is strange. Could you pleas help me with this? CUSIP Endwindow cValue2 r2 3783310 30-Dec-88 0.5625 0.5625 3783310 31-Jan-89 0.5616 0.5616 3783310 28-Feb-89 0.5113 0.5113 3783310 31-Mar-89 0.5193 0.5193 3783310 28-Apr-89 0.5078 0.5078 3783310 31-May-89 0.5152 0.5152 3783310 30-Jun-89 0.5077 0.5077 3783310 31-Jul-89 0.5031 0.5031 3783310 31-Aug-89 0.4442 0.4442 3783310 29-Sep-89 0.431 0.431 3783310 31-Oct-89 0.4183 0.4183 3783310 30-Nov-89 0.4154 0.4154 3783310 29-Dec-89 0.4165 0.4165 3783310 31-Jan-90 0.3833 0.3833 3783310 28-Feb-90 0.3059 0.3059 3783310 30-Mar-90 0.2968 0.2968 3783310 30-Apr-90 0.3056 0.3056 3783310 31-May-90 0.3628 0.3628 3783310 29-Jun-90 0.356 0.356 3783310 31-Jul-90 0.3486 0.3486   proc sql; create table want as select t1. cusip, t1.endwindow format=mmyyd., mean(t2.r2) as r2_avg, count(*) as months from Rsquare2 as t1 inner join Rsquare2 as t2 on t2.endwindow between intnx("month", t1.endwindow,-12,"beginning") and intnx("month", t1.endwindow,-2,"beginning") group by t1.cusip, t1.endwindow ; select * from want; quit;    CUSIP Endwindow r2_avg months 3783310 Mar-89 0.46613 3 3783310 Apr-89 0.4671 6 3783310 May-89 0.46352 9 3783310 Jun-89 0.46389 12 3783310 Jul-89 0.45912 15 3783310 Aug-89 0.45827 18 3783310 Sep-89 0.45742 21 3783310 Oct-89 0.45698 24 3783310 Nov-89 0.45267 27 3783310 Dec-89 0.44914 30 3783310 Jan-90 0.44388 30 3783310 Feb-90 0.43923 30 3783310 Mar-90 0.43656 30 3783310 Apr-90 0.4313 30 3783310 May-90 0.41893 30 3783310 Jun-90 0.40439 30 3783310 Jul-90 0.39083 30   Thank you so much, Ha                                                   ... View more

Hey, I figured it out. I just added a new variable  just like sum to calculate to the sum of the rows/columns. It worked. thanks for helping though. ... View more

Hey, A bit confused here. Thanks for your help in advance. This is what I have. Col1-49 is a symmetric 49*49 matrix and they represent distance from one another. Country     GDP         Col1      Col2     Col3.............Col49 Germany      5             0          3           1                  2 France         4              3           0          2                 1 Belgium        1              1         2           0                  1 So basically Market potential for Germany = (GDP of france)/Distance between germany and france (3 in this case)  + (GDP of Belgium)/(Distance between Ger and Bel (1 in this case). So I got a help from Ksharp here, but it says ERROR: Key not found. data have; set have; k=cats('Col',_n_); run; data want; if _n_ eq 1 then do;   if 0 then set have(keep=k rgdpinmi rename=(rgdpinmi=_gdp));   declare hash ha(dataset:'have(keep=k rgdpinmi rename=(rgdpinmi=_gdp))');    ha.definekey('k');    ha.definedata('_gdp');    ha.definedone(); end; set have; array x{*} col: ; sum=0; do i=1 to dim(x); if _n_ ne i then do;                    k=vname(x{i});                    ha.find();                      sum + _gdp/x{i};                       end; end; run; Can you help me solve the problem please? Thanks for your help. (The program worked when there were just 3 dataset (3*3 matrix), but doesn't work with my dataset 49*49 matrix. I realize it has to do with ha.find(), but couldn't figure out a way to solve it. Thanks. ... View more

try the code below: data have; input Date $         Port_no             Liquidity; cards; 011985        1                         2.3 021985        1                         1.4 031985        1                          2.4 041985        1                         3.4 122011         1                         2.4 011985         2                         4.5 021985         2                          5.4 122011          2                         4.5 ; data want(drop=count);   set have;   by port_no;   new1=lag(Liquidity);   new2=lag2(Liquidity);   if first.port_no then do;     new1=.;       new2=.;       count=0;   end;   count+1;   if count=2 then new2=.; run; proc print;run;                       Obs     Date     Port_no    Liquidity    new1    new2                       1     011985       1          2.3         .       .                        2     021985       1          1.4        2.3      .                        3     031985       1          2.4        1.4     2.3                        4     041985       1          3.4        2.4     1.4                        5     122011       1          2.4        3.4     2.4                        6     011985       2          4.5         .       .                        7     021985       2          5.4        4.5      .                        8     122011       2          4.5        5.4     4.5 Linlin ... View more

hi ... here are a couple alternatives to the already posted solutions ... data x; input date : date9. firm  share_outstanding ; datalines; 1jan85   1   2.2         2jan85   2   3.2                   3jan85   3   1                  1feb85   1   3                 2feb85   2   4        3feb85   3   5   ; proc sql; create table want as select date format=monyy., firm,  share_outstanding, mean(share_outstanding) as portmktcap from x group by put(date,monyy.) order by date; quit; and ... data have; input date port_no return liquidity; cards; 1985  1   2.5  3 1985  2   2.8  3.2 1985  3   2.3  3.4 1986  1   3.2  2.3 1986  2   3.5  2.1 1986  3   2.4  1.8 ; proc summary nway data=have (rename=(return=port_return liquidity=port_liquidity)); class date; output out=want (drop=_type_ _freq_) idgroup(out[3](port_return port_liquidity)=); run; ... View more