topic Re: Unable to convert MS Access SQL query to SAS Code in New SAS User

Unable to convert MS Access SQL query to SAS Code

jcis7 — Mon, 03 Dec 2018 19:10:59 GMT

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!

Re: Unable to convert MS Access SQL query to SAS Code

PGStats — Mon, 03 Dec 2018 19:33:12 GMT

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

Re: Unable to convert MS Access SQL query to SAS Code

Reeza — Mon, 03 Dec 2018 20:23:23 GMT

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!

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Tue, 04 Dec 2018 23:45:41 GMT

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?

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Tue, 04 Dec 2018 23:48:41 GMT

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.

Re: Unable to convert MS Access SQL query to SAS Code

Reeza — Wed, 05 Dec 2018 00:40:13 GMT

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

Re: Unable to convert MS Access SQL query to SAS Code

PGStats — Wed, 05 Dec 2018 05:09:24 GMT

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

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Wed, 05 Dec 2018 23:30:39 GMT

Thank you!

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Wed, 05 Dec 2018 23:43:39 GMT

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!

Re: Unable to convert MS Access SQL query to SAS Code

Reeza — Wed, 05 Dec 2018 23:56:59 GMT

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

Re: Unable to convert MS Access SQL query to SAS Code

Tom — Thu, 06 Dec 2018 01:53:46 GMT

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 )

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

or some other permutation?

Re: Unable to convert MS Access SQL query to SAS Code

Tom — Thu, 06 Dec 2018 01:56:26 GMT

What types of values do STARTDATE and LASTDATE contain?

Are they date values so that the difference is in days?

Or are they datetime values so that the difference is in seconds?

Re: Unable to convert MS Access SQL query to SAS Code

PGStats — Thu, 06 Dec 2018 05:01:00 GMT

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.

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Thu, 06 Dec 2018 15:46:39 GMT

Thanks!

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Thu, 06 Dec 2018 15:47:39 GMT

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

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Thu, 06 Dec 2018 16:03:39 GMT

Q1: What types of values do STARTDATE and LASTDATE contain?

A1: Start date I entered programmed into SAS as: startdate= '05-SEP-2017'd; but converts to 21067 in the SAS dataset

lastdate is a SAS date (datetime imported from MS Access (so it looks like in the SAS dataset 18APR2012:00:00:00 at first and then I converted to SAS date using datepart below so it becomes a SAS date) An example of lastdate is: lastdate=19254

Q2: Are they date values so that the difference is in days?

A2: They are in datetime in MS Access but when I imported to SAS, I used datepart to convert it to SAS day in an array called newdates:
do i=1 to dim(date);

newdate(i)=datepart(date(i));

end;

Q3: Or are they datetime values so that the difference is in seconds?

A3: They are in days.

Re: Unable to convert MS Access SQL query to SAS Code

jcis7 — Thu, 06 Dec 2018 19:29:39 GMT

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