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