topic For members of a character column give sum from numerical column in SAS Programming

For members of a character column give sum from numerical column

NKormanik — Fri, 05 Aug 2016 04:57:40 GMT

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

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

user24feb — Fri, 05 Aug 2016 05:23:31 GMT

but your char's are distinct.

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;

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

NKormanik — Fri, 05 Aug 2016 06:36:02 GMT

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!

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

NKormanik — Fri, 05 Aug 2016 07:55:37 GMT

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;

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

ballardw — Fri, 05 Aug 2016 15:13:30 GMT

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;

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

NKormanik — Tue, 09 Aug 2016 05:17:44 GMT

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