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,
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
The SAS INTNX() function allows you to shift dates (i.e. to the beginning or end of the previous month).
Hi Patrick,
Thank you for your reply.
Would you know if there is a Proc sql equivalent to the above syntax?
/* 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
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
/* 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
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
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
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.
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)
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.
it is Teradata
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.