BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ramin1
Obsidian | Level 7

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; 

Ruhul_0-1622243862367.png

 

in this case, I want 

Ruhul_1-1622244571874.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Angel_Larrion
SAS Employee

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;

 

View solution in original post

2 REPLIES 2
Angel_Larrion
SAS Employee

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;

 

Ramin1
Obsidian | Level 7
Works good, thanks Angel.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 626 views
  • 1 like
  • 2 in conversation