topic Re: sum by group in SAS Programming

sum by group

Ramin1 — Fri, 28 May 2021 23:54:42 GMT

Hi experts,

I want to sum one column based on some criteria and placed the sum value in a designated row. I add the data file here and what I want to get is the following

I want to sum TDC1 column based on gvkey, year, execrankann (execrankann 1 to 5) and want to put the sum value in the row which has value "ceo" in CEO column. For instance;

in this case, I want

Re: sum by group

Angel_Larrion — Sat, 29 May 2021 02:12:53 GMT

Try the following code:

proc sort data=HAVE;  /*order data set to use by statement in data step*/
by GVKEY YEAR EXECRANKANN;
quit;

data pre_want;      /*Creates the cumulative variable (SUM)*/
set HAVE;
by GVKEY YEAR;
if FIRST.YEAR THEN SUM=0;
SUM+TDC1;
run;

proc sql;
create table WANT as        /*Select the sum per group and paste it to the observation with CEOANN = "CEO"*/
select a.*, b.SUM as SUM_5_EXEC
from pre_want as a left join (select YEAR,GVKEY,SUM
							  from pre_want
							  where EXECRANKANN=5) as b

		on  (a.GVKEY=b.GVKEY and a.YEAR=b.YEAR and a.CEOANN="CEO")

order by GVKEY, YEAR, EXECRANKANN    
;
drop table pre_want;  /*Deletes the pre_want table*/
quit;

Re: sum by group

Ramin1 — Sat, 29 May 2021 04:51:35 GMT

Works good, thanks Angel.