Unable to convert MS Access SQL query to SAS Code

[jcis7]

G'day!

I'm trying to convert MS Access SQL Query code someone left in to SAS Code.

Here's the code in MS Access: 

SELECT TABLE1.STATUS AS IntStatus,

IIf([VAR1DOSENUM]=4 And [STARTDATE]-[LASTVAR1]<170,"COND",

IIf([VAR1DOSENUM]=3 And [STARTDATE]-[LASTVAR1]<170,"COND",

IIf([VAR1DOSENUM]=2 And [STARTDATE]-[LASTVAR1]<20,"COND",

IIf([VAR1DOSENUM]=1 And [STARTDATE]-[LASTVAR1]<20,"COND",

"NOT_UTD"))))

AS FinStatus_D

FROM TABLE1 INNER JOIN TABLE5 TABLE1.ID = TABLE5.ID;

______________________________________________

I've pasted my unsuccessful SAS code (not getting errors in log. Just not getting same # of "COND" and "NOT_UTD" as expected for VAR1) below

Any help you can give is much appreciated!!

data table6;
  set table5;
  
  *--define int_status;
  int_status=status;

  *--define cond or not_utd for var1;
if var1dosenum=4 and (startdate-lastvar1<170) then finstatus_d="COND";
    else if var1dosenum=3 and startdate-lastvar1<170 then finstatus_d="COND";
    else if var1dosenum=2 and startdate-lastvar1<20 then finstatus_d="COND";
    else if var1dosenum=1 and startdate-lastvar1<20 then finstatus_d="COND";
    else finstatus_d="NOT_UTD";

run;

Thank you!

[PGStats]

The logic looks equivalent to me. Are there some missing values among variables var1dosenum, startdate, or lastvar1?

[jcis7]

Thanks for your help!

Yes, there are ~800 missing for lastvar1 (same with the MS Access Query). I'm not sure though what to do next?

[PGStats]

The problem most likely comes from the way missing values compare in SAS and in Access.

If X is missing,

In Access IIF(X<10,"Small","Big") will return "Big" because the logical operation yields a missing (null) value and the function interprets Null as false.

For SAS, IFC(X<10,"Small","Big") will return "Small" because missing values are considered smaller than any non missing number.

You could make SAS behaviour similar to Access by inverting the comparisons:

data table6;
  set table5;
  
  *--define int_status;
  int_status=status;

  *--define cond or not_utd for var1;
         if var1dosenum=4 and lastvar1-startdate>-170 then finstatus_d="COND";
    else if var1dosenum=3 and lastvar1-startdate>-170 then finstatus_d="COND";
    else if var1dosenum=2 and lastvar1-startdate>-20  then finstatus_d="COND";
    else if var1dosenum=1 and lastvar1-startdate>-20  then finstatus_d="COND";
    else finstatus_d="NOT_UTD";

run;

hth

[jcis7]

Thank you!



Lastvar1 was missing for those observations and when SAS tried to compute ( lastvar1-startdate), the value was missing.

So SAS treated those children as falling into the "COND" category.



What does <- mean? I'm familiar with <= but not <-



Exactly as you said. Appreciate your explanation!

[Reeza]

The - is negative in front of the number, not part of the equality statement.

[jcis7]

Thanks!

[PGStats · Accepted solution]

The expression lastvar1-startdate>-170 reads "the difference between lastvar1 and startdate greater than -170"

When neither lastvar1 or startdate is missing, that expression is equivalent to startdate-lastvar1<170. But when lastvar1 is missing, the former yields false while the later gives true. 

[jcis7]

Got It! This works now! Thank you all so very much 🙂

[Reeza]

Those are nested, since it's IIF. 

I think using IFN would help you see it more clearly. This is untested, but the approach you likely want to use.

ifn(var1dosenum in (3,4) and (startdate-lastvar)<170, "COND", 
	ifn(var1dosenum in (1,2) and (startdate-lastvar)<20, "COND", 
	"NOT_UTD"))

---

@jcis7 wrote:

G'day!

I'm trying to convert MS Access SQL Query code someone left in to SAS Code.

Here's the code in MS Access: 

SELECT TABLE1.STATUS AS IntStatus,

IIf([VAR1DOSENUM]=4 And [STARTDATE]-[LASTVAR1]<170,"COND",

IIf([VAR1DOSENUM]=3 And [STARTDATE]-[LASTVAR1]<170,"COND",

IIf([VAR1DOSENUM]=2 And [STARTDATE]-[LASTVAR1]<20,"COND",

IIf([VAR1DOSENUM]=1 And [STARTDATE]-[LASTVAR1]<20,"COND",

"NOT_UTD"))))

AS FinStatus_D

FROM TABLE1 INNER JOIN TABLE5 TABLE1.ID = TABLE5.ID;

______________________________________________

I've pasted my unsuccessful SAS code (not getting errors in log. Just not getting same # of "COND" and "NOT_UTD" as expected for VAR1) below

Any help you can give is much appreciated!!

data table6;
  set table5;
  
  *--define int_status;
  int_status=status;

  *--define cond or not_utd for var1;
if var1dosenum=4 and (startdate-lastvar1<170) then finstatus_d="COND";
    else if var1dosenum=3 and startdate-lastvar1<170 then finstatus_d="COND";
    else if var1dosenum=2 and startdate-lastvar1<20 then finstatus_d="COND";
    else if var1dosenum=1 and startdate-lastvar1<20 then finstatus_d="COND";
    else finstatus_d="NOT_UTD";

run;

Thank you!

---

[jcis7]

Thanks for your help!



Got it - I tried the IFC and IFN and got the same numbers as using the initial code I posted so that is helpful to know a different way of coding!



I'm still getting a 40 count discrepancy between 'COND' in the MS Access query vs the SAS Freq Count (~40 more in the SAS Freq count).



I'm stumped.

[Reeza]

I think the issue should be more so, which one is correct. It sounds like you have a possible order of operations type issue.

[jcis7]

Thank you!

[Tom]

You should add some parentheses to your condition(s) to clarify what order you want the operators performed.

You posted:

[VAR1DOSENUM]=4 And [STARTDATE]-[LASTVAR1]<170

Is that 

([VAR1DOSENUM]=4) And (  ([STARTDATE]-[LASTVAR1]) < 170 )

or 

( ([VAR1DOSENUM]=4)  And [STARTDATE] ) - ([LASTVAR1]<170)

or some other permutation?

[jcis7]

It's: ([VAR1DOSENUM]=4) And ( ([STARTDATE]-[LASTVAR1]) < 170 )