topic Re: Extract data of a month based on today's date in New SAS User

Extract data of a month based on today's date

Kirito1 — Wed, 28 Dec 2022 05:55:21 GMT

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!!

Re: Extract data of a month based on today's date

andreas_lds — Wed, 28 Dec 2022 06:53:45 GMT

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

Re: Extract data of a month based on today's date

Kurt_Bremser — Wed, 28 Dec 2022 07:58:22 GMT

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.

Re: Extract data of a month based on today's date

Kirito1 — Wed, 28 Dec 2022 10:56:16 GMT

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

Re: Extract data of a month based on today's date

PaigeMiller — Wed, 28 Dec 2022 14:25:26 GMT

@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.

Re: Extract data of a month based on today's date

Patrick — Wed, 28 Dec 2022 15:13:22 GMT

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')

Re: Extract data of a month based on today's date

Kirito1 — Fri, 30 Dec 2022 06:04:39 GMT

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

Re: Extract data of a month based on today's date

Kirito1 — Fri, 30 Dec 2022 06:13:23 GMT

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.

Re: Extract data of a month based on today's date

Kurt_Bremser — Fri, 30 Dec 2022 08:28:05 GMT

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.

Re: Extract data of a month based on today's date

Patrick — Fri, 30 Dec 2022 10:24:46 GMT

@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.

Re: Extract data of a month based on today's date

SASKiwi — Fri, 30 Dec 2022 21:06:29 GMT

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;