Flag in PROC SQL syntax no longer working

[MHP]

Hey all,

I have been working on aggregating codes and creating flags for diagnosis data. I wrote one that worked fine but when I tried to apply it to another diagnosis, it does not work. Any help would be much appreciated. 

Here is the original syntax:

DATA WANT;
SET HAVE;
DIABETES = 0;
IF indexW("E0800" || " " || "E0801" || " " || "E0810" || " " || "E0811", MC041) > 0 then DIABETES = 1;
ELSE IF DIABETES = 0 THEN DELETE;

New syntax

DATA WANT;

SET NEED;

AMI = 0;
if indexW("41001" || " " || "41011" || " " || "41021" || " " || "41031" || " " || "41041", MC041) > 0 then AMI = 1;
ELSE IF AMI = 0 THEN DELETE;
RUN;

[Reeza]

"it does not work" doesn't actually tell us what doesn't work. 

How doesn't it work. 

Also, consider using WHICHC instead, it's a bit easier to read for one. Or at least CATX to create the string to search for. 

data diabetes;
set have;
diabetes=0;
if whichc(MC041, "E0800", "E0801", "E0810", "E0811")>0 then diabetes =1;
else delete;
run;

data ami;
set have;

ami=0;
if whichc(MC041, "41001", "41011", "41021", "41031")>0 then ami =1;
else delete;
run;

Actually the trick is in your log. 

Your first dataset references PD and only outputs records for Diabetes.

You then check for the AMI record from the data set from step 1. If this is correct it's likely your condition is never met. 

Or you need to switch to a different source dataset. 

PS. Please include your code and log in your post not as an attachment. 

[PGStats]

The error is not in the code that you presented. It is elsewhere in the code or in the data. Nevertheless, your code could be made simpler and clearer:

data diabetes;
set have;
diabetes = MC041 in ("E0800", "E0801", "E0810", "E0811");
if diabetes;
run;

data ami;
set have;
ami = MC041 in ("41001", "41011", "41021", "41031");
if ami;
run;

[Shmuel · Accepted solution]

In your log there is a note:  "Numeric values have been converted to characters";

It seems as MC041 in diabetes dataset was defined as CHAR type

while MC041 in ami was defined as numeric ?  Is it?

For testing AMI better do:

    if ami in (.  41001 41011 ... ) then ... ;   /* see PG Stats answer , and pay attention:

                                                                    as ami is numeric you cant check for blank but for missing value */