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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.