topic Re: Basic Formatting Question - Invalid Numeric Data error with IF statement in SAS Procedures

Basic Formatting Question - Invalid Numeric Data error with IF statement

JonathanWarrick — Wed, 16 Feb 2011 19:44:11 GMT

As a relative novice with SAS, formatting issues are still getting in the way of my coding. I'm currently trying to create a column that generates a "Y" flag if another field has one of several values in it. The code is:

If ELIGIBILITY_2 or ELIGIBILITY_3 or ELIGIBILITY_4 in ('65' '66' 'a' 'b' 'c') then ERR = "Y";
else ERR = "N";

The variables the IF statement checks (ELG_2,3,4) are already char. variables, but when the code runs, for anything with a character value (a, b, c), the error comes back:

"invalid numeric data ELG_2 = 'a' at line xyz"

Two questions:

First, why when I use this if statement does it automatically convert character values to numeric values. And second, what do I need to do to fix this?

Thanks in advance!

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

sbb — Wed, 16 Feb 2011 19:54:16 GMT

Your post content (the IF statement) and the SAS-generated diagnostic message are inconsistent (different SAS variable names used).

Suggest that you will get the most effective feedback by posting a reply with the exact SAS log (COPY/PASTE) with your code revealed in total and also the SAS NOTE, WARNING and ERROR messages, as they are generated.

Anything less creates a guessing-game, however I would tend to believe what SAS diagnostic information is being generated, to start - so, have a look at the SAS log, your code, and the specific SAS variables, by name -- remembering a SAS variable type defaults to NUMERIC, not CHARACTER, just in case there might be a misspelling.

Scott Barry
SBBWorks, Inc.

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

JonathanWarrick — Wed, 16 Feb 2011 19:57:49 GMT

I was just using abbreviated code for example's sake, but here is the actual code and actual error message:

data error_4;
set error_3;

*DEFINE PRA ERRORS;

if ELIGIBILITY_2 or
ELIGIBILITY_3 or
ELIGIBILITY_4 or
ELIGIBILITY_5 or
ELIGIBILITY_6 or
ELIGIBILITY_7 or
ELIGIBILITY_8 or
ELIGIBILITY_9 in
('64' '65' '66' '67' '68' '69' '70' 'h' 'i' 'k' 'l') then PRA_ERROR = 'Y';
else PRA_ERROR = 'N';

run;

NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
1248:4 1249:4 1250:4 1251:4 1252:4 1253:4 1254:4
NOTE: Invalid numeric data, ELIGIBILITY_2='j' , at line 1248 column 4 Message was edited by: JonathanWarrick

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

SusieQ324 — Wed, 16 Feb 2011 20:04:59 GMT

Jonathan,

I think you need to do something of the following:

array elig ELIGIBILITY_: ;
PRA_ERROR='N' ;
do over elig ;
if elig in (''64' '65' '66' '67' '68' '69' '70' 'h' 'i' 'k' 'l') then PRA_ERROR='Y' ;
end ;

So this code sets everyone to N unless the value in the ELIG variables is in that string. Is this what you were trying to do?

Sue

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

data_null__ — Wed, 16 Feb 2011 20:08:53 GMT

Your syntax is incorrect.

[pre]ELIGIBILITY_2 or ELIGIBILITY_3[/pre]
is an logical expression testing that either of the variables joined by the operator OR are 1(true). But since they are character variables that don't store numbers you get invalid data message.

you will need to test each eligibility as in this statement
[pre]ELIGIBILITY_9 in ('64' '65' '66' '67' '68' '69' '70' 'h' 'i' 'k' 'l')[/pre]

You can use an array and a do loop to make the code less onerous.

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

sbb — Wed, 16 Feb 2011 20:11:22 GMT

Your IF / THEN statement is coded incorrectly. You are using an incorrectly formatted OR condition for each of your variable tests.

As mentioned SAS variable type defaults to NUMERIC, so if you notice the NOTE: you see where SAS converting character to numeric, as well.

So, variables ELIGIBILITY_2 through ELIGIBILITY_8 are all being treated as NUMERIC tests (either ON or OFF, as in 1 or 0).

If you want to test each of these variables against the IN operator list, they must be tested individually against the literal string.

And, the presumption here is that your SAS variables listed in the IF / THEN are contained in the SAS member WORK.error_3 - given the NOTE where "j" is referenced, I would say that is the case.

Scott Barry
SBBWorks, Inc.

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

JonathanWarrick — Wed, 16 Feb 2011 20:11:25 GMT

Yes, that's exactly what I'm trying to do. I'll try using an array to see if I can get the results I want with that.

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

JonathanWarrick — Wed, 16 Feb 2011 20:31:14 GMT

I've changed the code now to use an array:

data error_4;
PRA_ERROR_FLAG = "N";
array pra_error 8

ELIGIBILITY_2
ELIGIBILITY_3
ELIGIBILITY_4
ELIGIBILITY_5
ELIGIBILITY_6
ELIGIBILITY_7
ELIGIBILITY_8
ELIGIBILITY_9;

do over pra_error;
if pra_error in ('64' '65' '66' '67' '68' '69' '70' 'h' 'i' 'k' 'l') then PRA_ERROR_FLAG = 'Y';
end;

set error_3;

Now the errors the log is producing are related to formatting:

ERROR: Variable ELIGIBILITY_2 has been defined as both character and numeric.
ERROR: Variable ELIGIBILITY_3 has been defined as both character and numeric.
ERROR: Variable ELIGIBILITY_4 has been defined as both character and numeric.
ERROR: Variable ELIGIBILITY_5 has been defined as both character and numeric.
ERROR: Variable ELIGIBILITY_6 has been defined as both character and numeric.
ERROR: Variable ELIGIBILITY_7 has been defined as both character and numeric.
ERROR: Variable ELIGIBILITY_8 has been defined as both character and numeric.
ERROR: Variable ELIGIBILITY_9 has been defined as both character and numeric.
1551 run;

Any suggestions?
run;

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

sbb — Wed, 16 Feb 2011 20:33:55 GMT

Check your ARRAY statement syntax.

Scott Barry
SBBWorks, Inc.

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

SusieQ324 — Wed, 16 Feb 2011 21:05:06 GMT

and I think you need commas between each of your selected values in the ().

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

SusieQ324 — Wed, 16 Feb 2011 21:05:45 GMT

Also, don't you need your SET statement before your ARRAY?

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

Peter_C — Thu, 17 Feb 2011 17:19:20 GMT

JonathanWarrick

SusieQ has the answer to your problem! (it is not the commas)

I'm surprised you wanted to make the test before loading the data in the SET statement!

peterC

Re: Basic Formatting Question - Invalid Numeric Data error with IF statement

Ksharp — Fri, 18 Feb 2011 07:01:43 GMT

Sbb is right.

>array pra_error 8

you definite a numeric array.But 'in' statement is character.
Definite a char array
array pra_error{*} $ 8 ................;

Ksharp