turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- For members of a character column give sum from nu...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2016 12:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2016 01:22 AM - edited 08-05-2016 01:23 AM

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;
```

All Replies

Solution

08-05-2016
02:25 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2016 01:22 AM - edited 08-05-2016 01:23 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2016 02:30 AM - edited 08-05-2016 02:36 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2016 03:55 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2016 11:13 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-09-2016 01:17 AM

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