How to sum in column by id?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

Attachment

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;

View solution in original post


All Replies
Valued Guide
Posts: 2,177

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,177

Re: How to sum in column by id?

Posted in reply to stataddict

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

Posted in reply to stataddict

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?

q.JPG

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;

Respected Advisor
Posts: 3,799

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: 7,854

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

Please confirm the same

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 4376 views
  • 6 likes
  • 6 in conversation