Creating a macro for the first time

[art297]

Then why not just use one sql call OR'ing the four conditions? If the week function provides what you want, you can easily add that to your select clause.

Art, CEO, AnalystFinder.com

[new_sas_user_4]

Thanks Art297 !!

I was wondering if that would pull unnecessary data but it didn't

Thank you again!!

[new_sas_user_4]

Hello everyone, 

I am a bit stuck on a query. Below are the codes and the results are attached :

Please help!!

proc sql;
create table X as select distinct DT,CYCLE_MTH
from table_name where
DT between intnx('month',today()-4,0,'b') and (today()-4) 
or DT between intnx('month',today()-4,-12,'b') and intnx('month',today()-4,-12,'s') /*PY MTD*/
and month(CYCLE_MTH)= month(today()-4)+1;
quit;

proc sql;
create table X1 as select distinct DT,CYCLE_MTH
from table_name where
DT between intnx('month',today()-4,0,'b') and (today()-4) 
and month(CYCLE_MTH)= month(today()-4)+1;
quit;

proc sql;
create table X2 as select distinct DT,CYCLE_MTH
from table_name where
DT between intnx('month',today()-4,-12,'b') and intnx('month',today()-4,-12,'s') /*PY MTD*/
and month(CYCLE_MTH)= month(today()-4)+1;
quit;

I have attached the results from the 3 pulls below.

My concern is : If I am pulling data separately as in X1 and X2 , the results are as I expect them to be!

but in extract X, I get values such as DT = 01JUN2018 and CYCLE_MTH = 01JUN2018 which isn't expected.

My desired result is X1 and X2 as one single table (CYCLE_MTH 01JUN2018 should not come up.) OR X but without CYCLE_MTH 01JUN2018 .

Could this be because I am using AND and OR conditions together? or could this be a database error?

Thank you!!!

[art297]

Always use parentheses to specify a complex where statement. Otherwise you risk getting what you asked for, rather than what you thought you asked for:

proc sql;
  create table X as select distinct DT,CYCLE_MTH
    from table_name
      where (DT between intnx('month',today()-4,0,'b') and (today()-4) 
        or DT between intnx('month',today()-4,-12,'b') and intnx('month',today()-4,-12,'s')) /*PY MTD*/
      and month(CYCLE_MTH)= month(today()-4)+1
  ;
quit;

Art, CEO, AnalystFinder.com

[new_sas_user_4]

Thank you Art297 🙂