For members of a character column give sum from numerical column

Solved
Regular Contributor
Posts: 238

For members of a character column give sum from numerical column

Suppose one has the following:

``````i_x	        N
_22606_0	20
_20404_6	3
_21001_0	3
_21301_4	14
_21605_0	14
_22501_0	10
_22602_6	10
_21105_1	1
_20706_0	16
_21603_0	4
_22201_4	4
``````

The desire is to get a total sum of N for each of the individual character values.

What would be the easiest way to do that in SAS?

Thanks!

Nicholas Kormanik

Accepted Solutions
Solution
‎08-05-2016 02:25 AM
Super Contributor
Posts: 355

Re: For members of a character column give sum from numerical column

[ Edited ]

``````Data Have;
Infile Datalines DELIMITER='09'x;
Input  i_x \$ N;
Datalines;
_22606_0	20
_20404_6	3
_21001_0	3
_21301_4	14
_21605_0	14
_22501_0	10
_22602_6	10
_21105_1	1
_20706_0	16
_21603_0	4
_22201_4	4
;

Proc Means Data=Have NoPrint NWay;
By Notsorted i_x;
Var N;
Output Out=Have_Sum (Drop=_:) Sum=;
Run;

Proc SQL;
Create Table Have_Sum_SQL As Select i_x,Sum(N) As N From Have Group By i_x;
Quit;``````

All Replies
Solution
‎08-05-2016 02:25 AM
Super Contributor
Posts: 355

Re: For members of a character column give sum from numerical column

[ Edited ]

``````Data Have;
Infile Datalines DELIMITER='09'x;
Input  i_x \$ N;
Datalines;
_22606_0	20
_20404_6	3
_21001_0	3
_21301_4	14
_21605_0	14
_22501_0	10
_22602_6	10
_21105_1	1
_20706_0	16
_21603_0	4
_22201_4	4
;

Proc Means Data=Have NoPrint NWay;
By Notsorted i_x;
Var N;
Output Out=Have_Sum (Drop=_:) Sum=;
Run;

Proc SQL;
Create Table Have_Sum_SQL As Select i_x,Sum(N) As N From Have Group By i_x;
Quit;``````
Regular Contributor
Posts: 238

Re: For members of a character column give sum from numerical column

[ Edited ]

Terrific.  Been scratching my head trying to get Proc Tabulate to do it.

Three quick follow-ups. Please briefly explain what the following were used for:

NWay
( Drop=_: )
Sum=

Thanks very much!

Regular Contributor
Posts: 238

Re: For members of a character column give sum from numerical column

Makes sense now.  Thanks so much!

My final code on this one:

``````proc sort data=nicholas.n_slope_means__7 ;
by i_x;
run;

proc means data=nicholas.n_slope_means__7
noprint
order=freq
maxdec=0
;
by i_x;
var n;

output out=nicholas.means_summary03
(drop=_:)
sum=
n=
min=
p10=
p20=
p30=
median=
p70=
p80=
p90=
max=
/ autoname
;
run;
``````

Super User
Posts: 13,501

Re: For members of a character column give sum from numerical column

You don't say whether you want a report or a dataset for the output.

Here is what I would consider the easiest:

This is a shorter dataset with some value of your i_x with different n to demonstrate. If you need a dataset you could use the out=option on the tables statement in proc freq.

``````Data Have;
Infile Datalines ;
Input  i_x \$ N;
Datalines;
_22606_0	20
_22606_0	12
_20404_6	3
_20404_6	27
_21001_0	3
_21301_4	14
;
run;

proc freq data=have;
weight n;
tables i_x;
run;``````

Proc Summary or means could sum the N values as well

``````proc summary data=have nway;
class i_x;
var N;
output out=want(drop= _:) sum=;
run;``````
Regular Contributor
Posts: 238

Re: For members of a character column give sum from numerical column

Thanks, ballardw, for contributing another solution.  Works perfectly.

☑ This topic is solved.