DATA Step, Macro, Functions and more

For members of a character column give sum from numerical column

Accepted Solution Solved
Reply
Regular Contributor
Posts: 223
Accepted Solution

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: 340

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

[ Edited ]
Posted in reply to NicholasKormanik

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;

View solution in original post


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

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

[ Edited ]
Posted in reply to NicholasKormanik

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;
Regular Contributor
Posts: 223

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

[ Edited ]
Posted in reply to user24feb

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: 223

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

Posted in reply to user24feb

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: 11,343

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

Posted in reply to NicholasKormanik

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: 223

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

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 266 views
  • 3 likes
  • 3 in conversation