Proc SQL equivalent to get previous month from current month date

[sufiya]

Hello,

Need your help, what is the proc sql equivalent for this sql syntax below :

ON t1.date -  EXTRACT(DAY FROM t1.date)= t2.date

This syntax provides data for the prior month.

please let me know.

thank you,

[Patrick]

The SAS INTNX() function allows you to shift dates (i.e. to the beginning or end of the previous month).

[sufiya]

Hi Patrick,

Thank you for your reply. 

Would you know if there is a Proc sql equivalent to the above syntax?

[kiranv_]

/* this is literal conversion */

ON t1.date - day( t1.date)= t2.date

/* you can also use*/

ON Intck('day', t1.date, -day(t1.date)) = t2.date

[sufiya]

Hello Kiranv,

thank you for your reply.

I added some additional conditions to the syntax but receive a syntax error. Please can you help me understand where I am going wrong with this... 

ON t1.Name=t1.Name and t1.date= t2.date - day( t1.date)= t2.date



ON t1.Name=t2.Name and t1.date= Intck('day', t2.date, -day(t1.date)) = t2.date 

[kiranv_]

/* I just indented your code

 some where your  third condition is not right
first condition which starts with ON looks syntactically OK
2nd one also looks ok which starts with and
3rd one looks odd, it does not have an and condition and also have does not something to compare to
*/

ON t1.Name=t1.Name 
and t1.date= t2.date - day( t1.date)
= t2.date

[sufiya]

the condition works in Teradata, I am looking for the proc SQL equivalent of this syntax...

teradata:

ON t1.name = t2.name AND t1.date - EXTRACT(DAY FROM t1. date) = t2. date

from the example you provided

/* this is literal conversion */

ON t1.date - day( t1.date)= t2.date

/* you can also use*/

ON Intck('day', t1.date, -day(t1.date)) = t2.date

used that to get this but it gives a syntax error ...

ON t1.Name=t2.Name and t1.date= Intck('day', t2.date, -day(t1.date)) = t2.date 

[Patrick · Accepted solution]

Don't use INTCK(). The SAS function to shift a date is INTNX(). But as written earlier you should avoid using SAS functions for data base tables which SAS can't push to the data base for execution.

Use the SAS DAY() function here. It does the same as the Teradata EXTRACT function with the DAY keyword.

ON t1.name = t2.name AND t1.date - DAY(t1.date) = t2.date

[sufiya]

Hi Patrick,

I used the join syntax as you suggested, didn't receive any error messages but it doesn't show the previous month data is blank.

ON t1.name = t2.name AND t1.date - DAY(t1.date) = t2.date

this is the data i get back..

Name	Date	ThisMonth	LastMonth
Cheerios	30-Nov-18	3	.
Lucky	30-Nov-18	3	.

which doesn't seem correct, as it isn't showing previous month data.

[Patrick]

@sufiya

So this expression in Teradata gives you the last day of the previous month: 

t1.date -  EXTRACT(DAY FROM t1.date)

If so then using SAS function INTNX() as posted by @PGStats will do the same: 

intnx("MONTH", t1.date, -1, "END")

Using the SAS function will work BUT if you still execute this against two tables in Teradata and use this function in a join condition then it could have a negative impact on performance. SAS can't push the INTNX() function to the database side and though will have to load all the data first into SAS before executing the function. Transferring all the data first to the SAS server can potentially create a big overhead.

You've got two options to overcome this:

1. Use explicit pass-through SQL. This allows you to formulate the SQL in Teradata syntax and execute the code directly on the database.

2. Use SAS SQL flavor BUT only use functions which SAS can push to the database for execution. Here the list of the functions for which this works.
https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p0lpu4mxo0lkrcn17ignr2iq9zb3.htm&docset...

For option 2:

The SAS DAY() function is in the list of functions which SAS can push to the database. You could therefor formulate your expression like: 
t1.date-day(t1.date)

[PGStats]

I don't know what dialect this is in, but looks like

t1.date -  EXTRACT(DAY FROM t1.date)

would be equivalent to

intnx("MONTH", t1.date, -1, "END")

i.e. the last day of the previous month, relative to t1.date.

[kiranv_]

it is Teradata