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!!!!!!!
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;
The error message indicates CD34 is a string not numeric.
what does it contain?
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;
@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?
I am not sure. But I think its just contain numeric. @@"
I looked to your table, all the variables are character except 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?
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;
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.