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!
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.
The logic looks equivalent to me. Are there some missing values among variables var1dosenum, startdate, or lastvar1?
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
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.
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!
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?
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!
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.