topic Re: Rolling average_another question in SAS Programming

Rolling average_another question

kimx0961 — Tue, 22 Dec 2015 03:55:36 GMT

Dear PGStats,

I mistakenly set the condition that at least three observations available for variable 'rds'. Condition should be that rds5y(average varible) should be available for at least continuous three years per company(gvkey). How can I change the below program?

Thank you very much

data have;
input gvkey fyear rds;
datalines;
0001    1980   0.05
0001    1981   0.02
0001    1982   0.03
0001    1983   0.04
0001    1984   0.04
0001    1985   0.05
0001    1986   0.06
;

proc sql;
create table want as
select 
    a.gvkey, 
    a.fyear, 
    a.rds, 
    case 
        when count(b.rds) >= 3 then mean(b.rds) 
        else . 
        end as rds5y
from 
    have as a left join
    have as b 
        on  a.gvkey=b.gvkey and 
            b.fyear between a.fyear-5 and a.fyear-1
group by a.gvkey, a.fyear, a.rds;
select * from want;
quit;

Re: Rolling average_another question

PGStats — Tue, 22 Dec 2015 04:10:02 GMT

So what should be the result for the above example?

Re: Rolling average_another question

kimx0961 — Tue, 22 Dec 2015 04:25:46 GMT

The following line should be deleted without affecting other program lines and new program line is needed to put the condition that at least continuous three rds5y(averge) should be avaiable per company(gvkey).

when count(b.rds) >= 3 then mean(b.rds)

Thank you

Re: Rolling average_another question

PGStats — Tue, 22 Dec 2015 04:35:25 GMT

The above code returns table want as

                          gvkey     fyear       rds     rds5y
                              1      1980      0.05         .
                              1      1981      0.02         .
                              1      1982      0.03         .
                              1      1983      0.04  0.033333
                              1      1984      0.04     0.035
                              1      1985      0.05     0.036
                              1      1986      0.06     0.036

what would you want instead?

Re: Rolling average_another question

kimx0961 — Tue, 22 Dec 2015 04:55:36 GMT

The following observations should be dropped because the number of observations for rds5y is less than 3(2 obs).

                          gvkey     fyear       rds     rds5y
                              1      1980      0.05         .
                              1      1981      0.02         .
                              1      1982      0.03         .
                              1      1983      0.04         .
                              1      1984      0.04         .
                              1      1985      0.05     0.036
                                       1986        .         0.036

The following observations should remain.

                        gvkey     fyear       rds     rds5y
                              1      1980      0.05         .
                              1      1981      0.02         .
                              1      1982      0.03         .
                              1      1983      0.04         .
                              1      1984      0.04         .
                              1      1985      0.05     0.036
                              1      1986      0.06     0.036

1 1987 0.03 0.044

Re: Rolling average_another question

kimx0961 — Wed, 23 Dec 2015 03:11:59 GMT

Hi,

Could you please advise the program that just removes the following condition? Thank you

when count(b.rds) >= 3 then mean(b.rds)

data have;
input gvkey fyear rds;
datalines;
0001    1980   0.05
0001    1981   0.02
0001    1982   0.03
0001    1983   0.04
0001    1984   0.04
0001    1985   0.05
0001    1986   0.06
;

proc sql;
create table want as
select
    a.gvkey,
    a.fyear,
    a.rds,
    case
        ~~when count(b.rds) >= 3 then mean(b.rds)~~
        else .
        end as rds5y
from
    have as a left join
    have as b
        on a.gvkey=b.gvkey and
            b.fyear between a.fyear-5 and a.fyear-1
group by a.gvkey, a.fyear, a.rds;
select * from want;
quit;

Re: Rolling average_another question

Reeza — Wed, 23 Dec 2015 05:24:32 GMT

Its not as easy as that. If you want to count continuous years you may need to use a data step rather than proc sql. If you search in here for rolling stats there's a bunch of methods that use a temporary array that would work

Re: Rolling average_another question

kimx0961 — Wed, 23 Dec 2015 14:19:14 GMT

Hi PGStats,

Could you please advise the program that just removes the following condition?

when count(b.rds) >= 3 then mean(b.rds)

I would like to have the following result.

rds5y

0001    1980   0.05    .
0001    1981   0.02    .
0001    1982   0.03    .
0001    1983   0.04    .
0001    1984   0.04    .
0001    1985   0.05    0.036
0001    1986   0.06    0.036