Extract data of a month based on today's date

[Kirito1]

The below code is throwing an error: 

Proc sql;
connect to ODBC as users
(dsn=****** uid=****** pwd="*****");
create table TXN as
select *
from connection to users
(select*
from RPT_OBIE.TABLE_TXNS_FIN
WHERE DAT_TXN >= DATEPART(MONTH, today())
AND QR_FLG='YES'
AND TXN_TYPE IN ('CREDIT','PAY')
AND TXN_STATUS IN ('SUCCESS')
);
quit;

 The Error: CLI describe error is always showing every time I try to use any date function such as today(), sysdate(), intck. I want the above code to extract data for the whole month based on todays date from the data base. The code is working fine when I am using a system data but its throwing an error when I am fetching data from Database.

ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00904: "DATEPART": invalid identifier

 Can anyone help!!

[andreas_lds]

Afaik you can't use sas functions in sql-pass-through.

[Kirito1]

Are you sure about that? I mean any article or blog or any paper states this if yes then Please provide.

[PaigeMiller]

---

@Kirito1 wrote:
Are you sure about that? I mean any article or blog or any paper states this if yes then Please provide.

---

Why do you need a blog or paper, when the LOG message is telling you clearly you can't use the DATEPART function in passthru? This means ... SAS is telling you that you can't do it. It doesn't get more authoritative than that.

[Kirito1]

Did you read my Query completely? ........I tried using every Possible function for date. This was just a sample.

[Kurt_Bremser]

In the explicit pass-through, you must use pure Oracle syntax. SAS functions will not work there.

The SAS DATEPART function accepts only one parameter, which has to be a datetime.

Instead of calculating the cutoff in Oracle, deliver it as a Oracle-compatible datetime literal, which you first store into a macro variable on the SAS side.

If you want to use explicit pass-through, you need to get versed in the target database's syntax first.

[Patrick]

You certainly can't use a SAS function within explicit pass-through SQL code. The SQL syntax within this explicit portion must be in the syntax of the database you're running it under. 

I can't test it but in your case interfacing with Oracle it's likely something like:

WHERE DAT_TXN >= TRUNC(sysdate, 'MONTH')

[Kirito1]

It bought the desired output but threw  an error for database code.

ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00936: missing expression

I mean its OK! but is it a problem I feel like It should not be. Any opinions.

[Kurt_Bremser]

You must study the Oracle documentation, or get help from your database people in writing the query.

You might also go to the Oracle Community.

[Patrick]

---

@Kirito1 wrote:

It bought the desired output but threw  an error for database code.

ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00936: missing expression

I mean its OK! but is it a problem I feel like It should not be. Any opinions.

---

If Oracle returns an Error condition then it's certainly an issue. 

I normally first develop and debug SQL pass-through code directly via a database client like DBeaver or SQL Developer. Only once I've got fully working code I "wrap" SAS around it. 

[SASKiwi]

Try this:

%let Txn_Date = %str(%')%sysfunc(today(), yymmddd10.)%str(%');
%put &Txn_Date;

Proc sql;
connect to ODBC as users
(dsn=****** uid=****** pwd="*****");
create table TXN as
select *
from connection to users
(select*
from RPT_OBIE.TABLE_TXNS_FIN
WHERE DAT_TXN >= &Txn_Date
AND QR_FLG='YES'
AND TXN_TYPE IN ('CREDIT','PAY')
AND TXN_STATUS IN ('SUCCESS')
);
quit;