DATA Step, Macro, Functions and more

How to sum up one column while keeping the others constant

Reply
New Contributor
Posts: 4

How to sum up one column while keeping the others constant

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!

Contributor
Posts: 53

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

[ Edited ]
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?

New Contributor
Posts: 4

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

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

 

22

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.

 

Contributor
Posts: 53

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

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

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

Hello,

 

As an alternative you can try:

 

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

Contributor
Posts: 44

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

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;

Ask a Question
Discussion stats
  • 5 replies
  • 226 views
  • 0 likes
  • 4 in conversation