topic Re: How to sum in column by id? in SAS Programming

How to sum in column by id?

Willy — Thu, 27 Feb 2014 07:48:14 GMT

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!!!!!!!

Re: How to sum in column by id?

Peter_C — Thu, 27 Feb 2014 09:35:01 GMT

The error message indicates CD34 is a string not numeric.

what does it contain?

Re: How to sum in column by id?

stataddict — Thu, 27 Feb 2014 11:44:39 GMT

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;

Re: How to sum in column by id?

Peter_C — Thu, 27 Feb 2014 12:25:09 GMT

@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?

Re: How to sum in column by id?

Willy — Thu, 27 Feb 2014 12:56:07 GMT

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

Re: How to sum in column by id?

stataddict — Thu, 27 Feb 2014 13:01:19 GMT

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

Re: How to sum in column by id?

Willy — Thu, 27 Feb 2014 13:10:15 GMT

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?

Re: How to sum in column by id?

stataddict — Thu, 27 Feb 2014 13:21:44 GMT

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;

Re: How to sum in column by id?

data_null__ — Thu, 27 Feb 2014 13:24:41 GMT

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.

Re: How to sum in column by id?

Kurt_Bremser — Thu, 27 Feb 2014 13:26:53 GMT

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

Re: How to sum in column by id?

AjayKant — Thu, 27 Feb 2014 13:59:42 GMT

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 ;

Please confirm the same