topic Re: Handling multiple conditions in WHERE clause in SAS Data Management

Handling multiple conditions in WHERE clause

David_Billa — Tue, 17 Dec 2019 07:16:18 GMT

I have multiple extract transformations in my SAS DI Job and all those extract transformations has the following condition under 'where' tab

VALID_FROM_DTTM <= DATETIME () AND VALID_TO_DTTM >=DATETIME ()
& 
REPORTING_METHOD = "&REPORTINGMETHOD" 
&
REPORTING_DT = &REPORTINGDATE

Now I was asked to add the one more condition based on REPORTING_METHOD. If REPORTING_METHOD is "ACS" then I have to add the condition like

put(INITIAL_RECOGNITION_DT,yymmn6.) <= put(REPORTING_DT,yymmn6.)

If REPORTING_METHOD is "OCS" then I have to add the condition like

put(INITIAL_RECOGNITION_DT,yymmn6.) >= put(REPORTING_DT,yymmn6.)

It means I have to write a code to conditionally change the filter condition which I'm not certain how to handle it in Extract transformation.

VALID_FROM_DTTM <= DATETIME () AND VALID_TO_DTTM >=DATETIME ()
& 
REPORTING_METHOD = "&REPORTINGMETHOD" 
&
REPORTING_DT = &REPORTINGDATE
&
put(INITIAL_RECOGNITION_DT,yymmn6.) <= put(REPORTING_DT,yymmn6.) /*if REPORTING_METHOD  is "ACS" */

Appericiate if someone of you guide me here.

Re: Handling multiple conditions in WHERE clause

mkeintz — Tue, 17 Dec 2019 08:07:28 GMT

As I understand it, you MUST test

put(INITIAL_RECOGNITION_DT,yymmn6.) >= put(REPORTING_DT,yymmn6.)

REPORTING_METHOD="ACS"but

but you don't need to filter on INITIAL_RECOGNITION_DT otherwise.

If so, I believe you could use

(REPORTING_METHOD^="ACS"  or  put(INITIAL_RECOGNITION_DT,yymmn6.) <= put(REPORTING_DT,yymmn6.))

BTW, since initial_recognition_dt and reporting_dt are date variables, when you want to determine whether they are in different months, you don't need a pair of PUT functions. Instead you can use

intck('month',initial_recognition_dt,reportig_dt)>0

the intnx function above tells how many month boundaries are crossed between the dates.

Re: Handling multiple conditions in WHERE clause

David_Billa — Tue, 17 Dec 2019 08:38:04 GMT

I can't understand your Guidelines. What I want is to check for the REPORTING_METHOD value. If it equals "ACS" then I want to add the below condition along with other condition as mentioned in Initial post.

put(INITIAL_RECOGNITION_DT,yymmn6.) <= put(REPORTING_DT,yymmn6.)

If it equals "OCS" then I want to add the below condition

put(INITIAL_RECOGNITION_DT,yymmn6.) >= put(REPORTING_DT,yymmn6.)

Re: Handling multiple conditions in WHERE clause

mkeintz — Tue, 17 Dec 2019 14:46:44 GMT

Please re-read my note:

which includes

If so, I believe you could use

(REPORTING_METHOD^="ACS"  or  put(INITIAL_RECOGNITION_DT,yymmn6.) <= put(REPORTING_DT,yymmn6.))

Then I added that a comment on the second condition, i.e.

    ..... put(INITIAL_RECOGNITION_DT,yymmn6.) <= put(REPORTING_DT,yymmn6.))

where I suggested making it more compact by using the INTNX function, as in

intck('month',initial_recognition_dt,reportig_dt)>0

Re: Handling multiple conditions in WHERE clause

Tom — Tue, 17 Dec 2019 15:02:57 GMT

What exactly are you having trouble with? Also why are you sometimes spelling out the AND operator and sometimes using & as the AND operator? That will just cause you confusion.

To add those conditions it will probably be easier to reverse the logic a little. So when REPORTING_METHOD is ACS you want to reject the values where recognition date is after the reporting date.

where VALID_FROM_DTTM <= DATETIME()
  and VALID_TO_DTTM >= DATETIME()
  and REPORTING_METHOD = "&REPORTINGMETHOD" 
  and REPORTING_DT = &REPORTINGDATE
  and not (REPORTING_METHOD="ACS" AND put(INITIAL_RECOGNITION_DT,yymmn6.) > put(REPORTING_DT,yymmn6.))
  and not (REPORTING_METHOD="OCS" AND put(INITIAL_RECOGNITION_DT,yymmn6.) < put(REPORTING_DT,yymmn6.))
;

Re: Handling multiple conditions in WHERE clause

David_Billa — Tue, 17 Dec 2019 15:32:49 GMT

Yes, your understanding is correct on my requirement. I do have test data
at the moment to test your code. Couple of questions though.

What if the REPORTING_METHOD values contains either 'ACS' or 'OCS'? Will
your code still works?

If I ask you write the condition without reversing the logic, Is itn't a
matter of replacing AND with OR and vice-versa and removing NOT?

Re: Handling multiple conditions in WHERE clause

Tom — Tue, 17 Dec 2019 16:57:40 GMT

If I ask you write the condition without reversing the logic, Is itn't a matter of replacing AND with OR and vice-versa and removing NOT?

The form I posted was NOT (A and B). So make yourself a little truth table and work it out for yourself.

A | B | A and B | not (A and B) 
--|---|---------|--------------
Y | Y | Y       | N
Y | N | N       | Y
N | Y | N       | Y
N | N | N       | Y

What if the REPORTING_METHOD values contains either 'ACS' or 'OCS'? Will
your code still works?

Depends on what you mean by contains. The test is whether the value is exactly ACS or OCS. It it is neither of those exact value then the equality test fails so the result of the date test will not matter. So for any values other than exactly ACS or OCS the date order does not matter as this NOT (A and B) test will be true because the A part is false. Added another (AND 1=1) to a list of boolean expressions being ADDed together does not change the result.