Help using Base SAS procedures

Basic Formatting Question - Invalid Numeric Data error with IF statement

Reply
Occasional Contributor
Posts: 15

Basic Formatting Question - Invalid Numeric Data error with IF statement

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!
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to JonathanWarrick
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.
Occasional Contributor
Posts: 15

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

Posted in reply to JonathanWarrick
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)Smiley SadColumn).
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
Occasional Contributor
Posts: 18

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

Posted in reply to JonathanWarrick
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
Occasional Contributor
Posts: 15

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

Posted in reply to SusieQ324
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.
Occasional Contributor
Posts: 15

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

Posted in reply to JonathanWarrick
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;
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to JonathanWarrick
Check your ARRAY statement syntax.

Scott Barry
SBBWorks, Inc.
Occasional Contributor
Posts: 18

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

and I think you need commas between each of your selected values in the ().
Occasional Contributor
Posts: 18

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

Posted in reply to SusieQ324
Also, don't you need your SET statement before your ARRAY?
Valued Guide
Posts: 2,177

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

Posted in reply to SusieQ324
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
Super User
Posts: 10,035

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

Posted in reply to JonathanWarrick
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
Respected Advisor
Posts: 3,799

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

Posted in reply to JonathanWarrick
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.
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to JonathanWarrick
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.
Ask a Question
Discussion stats
  • 12 replies
  • 809 views
  • 0 likes
  • 6 in conversation