BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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. 

PG

View solution in original post

16 REPLIES 16
PGStats
Opal | Level 21

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

PG
jcis7
Pyrite | Level 9
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
Opal | Level 21

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

 

PG
jcis7
Pyrite | Level 9
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
Super User
The - is negative in front of the number, not part of the equality statement.
PGStats
Opal | Level 21

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. 

PG
jcis7
Pyrite | Level 9
Got It! This works now! Thank you all so very much 🙂


Reeza
Super User

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
Pyrite | Level 9
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
Super User
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
Pyrite | Level 9
Thank you!


Tom
Super User Tom
Super User

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
Pyrite | Level 9
It's: ([VAR1DOSENUM]=4) And ( ([STARTDATE]-[LASTVAR1]) < 170 )




hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 16 replies
  • 3121 views
  • 4 likes
  • 4 in conversation