topic Re: How to sum up one column while keeping the others constant in SAS Programming

How to sum up one column while keeping the others constant

PETE — Tue, 13 Oct 2015 20:48:45 GMT

Good afternoon – in the below data set that I have narrowed down, I’m running into the problem of how to sum up 1 column, while keeping the others constant. For example, I want to sum up the # of people that are eligible for MASS, BOSTON, SEAFOOD,

Dataset example is:

STATE	CITY	FOOD	NAME	Eligible2
MASS	BOSTON	SEAFOOD	MARY	1
MASS	BOSTON	SEAFOOD	FRANK	1
MASS	BOSTON	SEAFOOD	DAN	1
MASS	BOSTON	SEAFOOD	SAM	0

While what I’m hoping for an output would be:

STATE	CITY	FOOD	Eligible
MASS	BOSTON	SEAFOOD	3

Any suggestions?

Thank you!

Re: How to sum up one column while keeping the others constant

dsbihill — Tue, 13 Oct 2015 20:54:55 GMT

Proc SQL;
    create table want as
         select State, city, food, sum(eligible2) as eligible
            from havetable
     group by state, city, food
;
quit;

Something like this?

Re: How to sum up one column while keeping the others constant

PETE — Tue, 13 Oct 2015 21:14:03 GMT

Thank you dsbihill. That got me down to just one error:

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT,

SUBSTRING, USER.

Re: How to sum up one column while keeping the others constant

dsbihill — Tue, 13 Oct 2015 21:18:45 GMT

Can you post your code or the part of the log above the error? You probably have an extra comma somewhere.

Re: How to sum up one column while keeping the others constant

Loko — Wed, 14 Oct 2015 09:10:41 GMT

Hello,

As an alternative you can try:

proc means data=sashelp.cars nway sum nonobs;
class make type origin;
var invoice;
run;

Re: How to sum up one column while keeping the others constant

AskoLötjönen — Wed, 14 Oct 2015 09:17:31 GMT

Also Proc Summary can be used for purpose like this.

proc summary data=have sum nway ;
class State City Food;
var Eligible2 ;
output out=want(drop=_type_ _freq_) sum=Eligible;
run;