BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Willy
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
stataddict
Calcite | Level 5

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

10 REPLIES 10
Peter_C
Rhodochrosite | Level 12

The error message indicates CD34 is a string not numeric.

what does it contain?

stataddict
Calcite | Level 5

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;

Peter_C
Rhodochrosite | Level 12

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

Willy
Calcite | Level 5

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

stataddict
Calcite | Level 5

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

Willy
Calcite | Level 5

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

stataddict
Calcite | Level 5

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;

data_null__
Jade | Level 19

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.

Kurt_Bremser
Super User

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

AjayKant
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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