topic Counting by group in New SAS User

Counting by group

mmh — Thu, 18 Nov 2021 07:45:17 GMT

Dear Altruists,
I am a new user of SAS.

I have the following table:

GVKey	Month	Announcements	Ann_Yes/No
1001	Jan		0
1001	Feb		0
1001	Mar	Mar	1
1001	Apr		0
1001	May		0
1001	Jun	Jun	1
1001	Jul		0
1001	Aug		0
1001	Sep	Sep	1
1001	Oct		0
1001	Nov		0
1001	Dec	Dec	1
1002	Jan		0
1002	Feb	Feb	1
1002	Mar		0
1002	Apr		0
1002	May		0
1002	Jun	Jun	1
1002	Jul		0
1002	Aug		0
1002	Sep	Sep	1
1002	Oct		0
1002	Nov	Nov	1
1002	Dec		0

I am trying to create an output column "Ann_in_last_12_months" where I count the previous 12 rows by group on the basis of the "Ann_Yes/No" column and add up the resulting value as shown below:

GVKey	Month	Announcements	Ann_Yes/No	Ann_in_last_12_months
1001	Jan		0	0
1001	Feb		0	0
1001	Mar	Mar	1	1
1001	Apr		0	1
1001	May		0	1
1001	Jun	Jun	1	2
1001	Jul		0	2
1001	Aug		0	2
1001	Sep	Sep	1	3
1001	Oct		0	3
1001	Nov		0	3
1001	Dec	Dec	1	4
1002	Jan		0	0
1002	Feb	Feb	1	1
1002	Mar		0	1
1002	Apr		0	1
1002	May		0	1
1002	Jun	Jun	1	2
1002	Jul		0	2
1002	Aug		0	2
1002	Sep	Sep	1	3
1002	Oct		0	3
1002	Nov	Nov	1	4
1002	Dec		0	4

Thank you in advance for your kind support!

Re: Counting by group

PeterClemmensen — Thu, 18 Nov 2021 08:12:34 GMT

Here is one way

data have;
input GVKey $ Month $ Announcements $ y_n;
infile datalines dlm = ',';
datalines;
1001,Jan,   ,0
1001,Feb,   ,0
1001,Mar,Mar,1
1001,Apr,   ,0
1001,May,   ,0
1001,Jun,Jun,1
1001,Jul,   ,0
1001,Aug,   ,0
1001,Sep,Sep,1
1001,Oct,   ,0
1001,Nov,   ,0
1001,Dec,Dec,1
1002,Jan,   ,0
1002,Feb,Feb,1
1002,Mar,   ,0
1002,Apr,   ,0
1002,May,   ,0
1002,Jun,Jun,1
1002,Jul,   ,0
1002,Aug,   ,0
1002,Sep,Sep,1
1002,Oct,   ,0
1002,Nov,Nov,1
1002,Dec,   ,0
;

data want;
    array l[0:11] _temporary_;            
    call missing(of l[*], obs);   
    
    do obs = 1 by 1 until (last.GVKey);       
        set have;
        by GVKey;
        l[mod(obs, 12)] = y_n;            
        count = sum(of l[*]);              
        output;
    end;
    
run;

Re: Counting by group

mmh — Thu, 18 Nov 2021 08:23:05 GMT

Thank you so much Peter!
I am really grateful for your kind support 🙂

Re: Counting by group

PeterClemmensen — Thu, 18 Nov 2021 08:26:12 GMT

No problem 🙂

Re: Counting by group

AndreaVianello — Thu, 18 Nov 2021 08:47:09 GMT

data have;
input GVKey $ Month $ Announcements $ y_n;
infile datalines dlm = ',';
datalines;
1001,Jan, ,0
1001,Feb, ,0
1001,Mar,Mar,1
1001,Apr, ,0
1001,May, ,0
1001,Jun,Jun,1
1001,Jul, ,0
1001,Aug, ,0
1001,Sep,Sep,1
1001,Oct, ,0
1001,Nov, ,0
1001,Dec,Dec,1
1002,Jan, ,0
1002,Feb,Feb,1
1002,Mar, ,0
1002,Apr, ,0
1002,May, ,0
1002,Jun,Jun,1
1002,Jul, ,0
1002,Aug, ,0
1002,Sep,Sep,1
1002,Oct, ,0
1002,Nov,Nov,1
1002,Dec, ,0
; run;

data want; set have;
retain Ann_in_last_12_months 0;
if y_n=1 then Ann_in_last_12_months+1;
run;

bye