BookmarkSubscribeRSS Feed
JonathanWarrick
Calcite | Level 5
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!
12 REPLIES 12
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
JonathanWarrick
Calcite | Level 5
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
SusieQ324
Fluorite | Level 6
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
JonathanWarrick
Calcite | Level 5
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.
JonathanWarrick
Calcite | Level 5
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;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Check your ARRAY statement syntax.

Scott Barry
SBBWorks, Inc.
SusieQ324
Fluorite | Level 6
and I think you need commas between each of your selected values in the ().
SusieQ324
Fluorite | Level 6
Also, don't you need your SET statement before your ARRAY?
Peter_C
Rhodochrosite | Level 12
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
Ksharp
Super User
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
data_null__
Jade | Level 19
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1888 views
  • 0 likes
  • 6 in conversation