topic Re: Cumulative sum by firm and year in SAS Programming

Cumulative sum by firm and year

joon1 — Tue, 11 May 2021 05:15:02 GMT

Dear Madam/Sir,

I would like to get the cumulative sum for five executives by firm and year as below.

firm year executive id shrown_tot_pct

949596979899100101102103

001004	2009	09249	3.861
001004	2009	09252	0.694
001004	2009	23781	0.458
001004	2009	33979	0.286
001004	2009	36199	0.121
001004	2010	09249	3.833
001004	2010	09252	0.897
001004	2010	33979	0.361
001004	2010	36199	0.177
001004	2010	41787	0.265

I used the following code, but I did not get the desired results (null).

proc sort data=y1; by gvkey year; run;
data y2; set y1;
by gvkey year;
retain sumown;
if first.gvkey then sumown=shrown_tot_pct;
else sumown=sumown+shrown_tot_pct; run;

Any advice will be highly appreciated.

Thanks

Joon1

Re: Cumulative sum by firm and year

andreas_lds — Tue, 11 May 2021 06:00:13 GMT

Description, table and code do not match.

Please post data in usable form and take care that you are using the same names in description, data and code.

Re: Cumulative sum by firm and year

mkeintz — Wed, 12 May 2021 12:21:57 GMT

 wrote:

Dear Madam/Sir,
I would like to get the cumulative sum for five executives by firm and year as below.
                                                                                                        firm        year   executive id    shrown_tot_pct                              

949596979899100101102103
001004
2009
09249
3.861
001004
2009
09252
0.694
001004
2009
23781
0.458
001004
2009
33979
0.286
001004
2009
36199
0.121
001004
2010
09249
3.833
001004
2010
09252
0.897
001004
2010
33979
0.361
001004
2010
36199
0.177
001004
2010
41787
0.265
                   I used the following code, but I did not get the desired results (null).
 

proc sort data=y1; by gvkey year; run;
data y2; set y1;
by gvkey year;
retain sumown;
if first.gvkey then sumown=shrown_tot_pct;
else sumown=sumown+shrown_tot_pct; run;
 
Any advice will be highly appreciated.
 
Thanks
Joon1
 

It is not apparent why you got nulls for SUMOWN.  Please show the log, and a sample of the resulting data.
 
BUT, even if you didn't get nulls, you would NOT get want you expect.  Your program only resets SUMOWN when you start a gvkey.  You presumably want to do this every time you start a new year within a gvkey.   So instead of "if first.gvkey ...", use "if first.year".  

Re: Cumulative sum by firm and year

joon1 — Wed, 12 May 2021 01:02:40 GMT

Thank you! I appreciate it.

Joon1