How to fix Invalid numeric data

[ndamo]

Dear Community , 

I'm working on the data to report the category of numeric variable( Weight ). 

my dataset looks like below:
ID Weight    Wtcat
1 50 
2 60
3 80
4 60,75  
5 75
6 D

Wtcat is the new column to report the categories of weight e.g <50= cat1 , 50-60= cat2 etc.....) 

Here is my pgm: 

DATA new;
SET old;
LENGTH Wtcat $20;
IF WEIGHT <50.0 THEN Wtcat ='<50 Kg';
IF WEIGHT=>50.0 and Wtcat <60.0 THEN Wtcat ='50-60 Kg';
IF WEIGHT=>60.0 and Wtcat <70.0 THEN Wtcat ='60-70 Kg';
.......................................................

RUN;

I got the note below in my log:
ID=4 Wtcat=<50 Kg _ERROR_=1
_N_=109
NOTE: Invalid numeric data, WEIGHT='57,7' , at line 290 column 3.

The same issue for ID=6
NOTE: Invalid numeric data, WEIGHT='D' , at line 286 column 4

And ID=4 was reported as <50 rather than >=50 because of the comma in weight value.
So I would like to fix this.

Thank you

[PaigeMiller]

I got the note below in my log:
ID=4 Wtcat=<50 Kg _ERROR_=1
_N_=109
NOTE: Invalid numeric data, WEIGHT='57,7' , at line 290 column 3.

This can't possibly come from the data you are showing. Please show us the actual data you are using in data set OLD, by creating working SAS data step code typing it in yourself (and testing it to make sure it works) or via these instructions.

NOTE: Invalid numeric data, WEIGHT='D' , at line 286 column 4

SAS can't read the letter D in as a numeric. So WEIGHT will be set to missing on this row.

And ID=4 was reported as <50 rather than >=50 because of the comma in weight value.

Change the comma to a decimal point (a dot) and it will work. Or, use the COMMAX. informat used to read in the data to use commas to separate the integer from the decimal.

[ndamo · Accepted solution]

Thank you. My raw datasets contains many observations . My pgm consist of the category of the last weight for each obs.

**Report the last weight and remove all missing data
DATA LASTWEIGHT;
SET DMWEIGHT;
WHERE WEIGHT IS NOT MISSING;
BY ID;
IF LAST.ID;
RUN;

PROC PRINT;
RUN;

*Split the weight to report the the weight category
DATA WGHTLAST(KEEP=ID VISIT WEIGHT WGHTCAT);
SET LASTWEIGHT;
LENGTH WGHTCAT $20;
IF WEIGHT <50.0 THEN WGHTCAT='<50 Kg';
IF WEIGHT=>50.0 and WEIGHT<60.0 THEN WGHTCAT='50-60 Kg';
IF WEIGHT=>60.0 and WEIGHT<70.0 THEN WGHTCAT='60-70 Kg';
IF WEIGHT=>70.0 and WEIGHT<80.0 THEN WGHTCAT='70-80 Kg';
IF WEIGHT=>80.0 and WEIGHT<90.0 THEN WGHTCAT='80-90 Kg';
IF WEIGHT=>90.0 and WEIGHT<100.0 THEN WGHTCAT='90-100Kg';
IF WEIGHT=>100.0 and WEIGHT<110.0 THEN WGHTCAT='100 – 110 Kg';
IF WEIGHT=>110.0 and WEIGHT<120.0 THEN WGHTCAT='110 – 120 Kg';
IF WEIGHT=>120.0 THEN WGHTCAT='>=120Kg';

RUN;

'D' means Not Done . I think I have to remove it but the issue is that user can enter the number both with comma and dot .

[tarheel13]

your code does not look correct. also you should wrap it in if ^missing(weight) then do. How do you want to classify wtcat if weight is missing? you realize that missing weight is also <50 and would be classified as wtcat='<50 kg'. I don't think that's what you intended. also you need to use weight in your 2nd and 3rd if statement. 

[tarheel13]

you're getting that error because weight is evidently not numeric, but rather character, and you are trying to compare a character to a number. you need to convert your character weight variable to numeric to do a NUMERIC comparison. also, what does weight D mean?