## How to sum in column by id?

Solved
Occasional Contributor
Posts: 7

# How to sum in column by id?

Hello!

I want to sum cd34 by the same id and name the sum as OPD_cost.

However I meet some problem while programming it.

133  proc means data= a.question sum;

134     var cd34;

ERROR: Variable cd34 in list does not match type prescribed for this list.

135     by id;

136     output out = test3 sum = OPD_cost;

137  run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TEST3 may be incomplete.  When this step was stopped there were 0

observations and 0 variables.

WARNING: Data set WORK.TEST3 was not replaced because this step was stopped.

NOTE: PROCEDURE MEANS used (Total process time):

real time           0.00 seconds

cpu time            0.00 seconds

Here is the code I have tried.

proc means data= a.question sum;

var cd34;

by id;

output out = test3 sum = OPD_cost;

run;

Can anyone tell me how to fix the problem or the right code? Thanks a lot!!!!!!!

Accepted Solutions
Solution
‎02-27-2014 08:21 AM
Occasional Contributor
Posts: 14

## Re: How to sum in column by id?

Here are the values of CD34 variable

00000341

00000919

00001174

00001383

I guess comma format converts not in  a proper way.

So, instead of comma21.2 use only 8.;

proc sql;

create table OPD_cost as

select id, sum(input(CD34, 8.)) as Sum_CD34

from a.question

group by id

;

quit;

All Replies
Valued Guide
Posts: 2,191

## Re: How to sum in column by id?

The error message indicates CD34 is a string not numeric.

what does it contain?

Occasional Contributor
Posts: 14

## Re: How to sum in column by id?

It seems that variable CD34 is character.

first transform it to numeric one: CD34_NUM = input(CD34, comma21.2);

instead of comma21.2 you can use some other numeric format.

then what are you going to achieve?

table like this?

ID           Sum_CD34

001          100

002          250

003          350

etc...

If this is what you want, easy way is to use proc sql;

proc sql;

create table OPD_cost as

select id, sum(input(CD34, comma21.2)) as Sum_CD34

from a.question

group by id

;

quit;

Valued Guide
Posts: 2,191

## Re: How to sum in column by id?

@stataddict provides a solution to deal with the convesion to numeric.

My question is about the contents - it is very unusual to see a column stored as text that is suitable for summing. Does it also contain non-numeric strings?

Occasional Contributor
Posts: 7

## Re: How to sum in column by id?

I am not sure. But I think its just contain numeric. @@"

Occasional Contributor
Posts: 14

## Re: How to sum in column by id?

I looked to your table, all the variables are character except id...

Occasional Contributor
Posts: 7

## Re: How to sum in column by id?

I have tried your method. But I still meet one small problem.

The sum is 38.17. But I want the sum as 3817.

Can I just sum it without any comma? Or maybe I should change other numeric format?

Solution
‎02-27-2014 08:21 AM
Occasional Contributor
Posts: 14

## Re: How to sum in column by id?

Here are the values of CD34 variable

00000341

00000919

00001174

00001383

I guess comma format converts not in  a proper way.

So, instead of comma21.2 use only 8.;

proc sql;

create table OPD_cost as

select id, sum(input(CD34, 8.)) as Sum_CD34

from a.question

group by id

;

quit;

Posts: 3,852

## Re: How to sum in column by id?

When you read a value with no decimal using and informat with a Decimal specification that's how it works.  You got what you ask for.

If you remove the d from comma21.2 you should get what you expect.

Super User
Posts: 10,259

## Re: How to sum in column by id?

In

CD34_NUM = input(CD34, comma21.2);

replace the format with comma21.

The format works like that:

- read in all numeric digits as a natural number

- if there is a number X after the dot in the format, divide by 10^X

Your numbers were converted as 1.00, 2.50, 3.50 respectively instead of 100, 250, 350

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

## Re: How to sum in column by id?

Hi I hope You get your answer and confirm the same .

*first subset your variable from the data set by id and cd43 ;

data have ;

set question ;

cd43=(input(CD34, 8.)) ;

keep id cd43 ;

run ;

*then calculation by id variable ;

data want ;

set have ;

by id ;

if first.id then Sum_cs43 = 0 ;

Sum_cs43 + cd43 ;

if last.id ;

drop cd43 ;

run ;