DATA Step, Macro, Functions and more

Flag in PROC SQL syntax no longer working

Accepted Solution Solved
Reply
New Contributor MHP
New Contributor
Posts: 3
Accepted Solution

Flag in PROC SQL syntax no longer working

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;

 


Accepted Solutions
Solution
‎09-23-2016 03:56 PM
Trusted Advisor
Posts: 1,566

Re: Flag in PROC SQL syntax no longer working

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 */

View solution in original post


All Replies
Super User
Posts: 19,815

Re: Flag in PROC SQL syntax no longer working

"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. 

Respected Advisor
Posts: 4,925

Re: Flag in PROC SQL syntax no longer working

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;
PG
Solution
‎09-23-2016 03:56 PM
Trusted Advisor
Posts: 1,566

Re: Flag in PROC SQL syntax no longer working

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 */

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 370 views
  • 0 likes
  • 4 in conversation