topic Re: How to convert number to date in pass thru query and fetch data particular date ? in SAS Programming

How to convert number to date in pass thru query and fetch data particular date ?

MSK4 — Thu, 29 Jul 2021 10:33:45 GMT

Having columns

name amount trandate(in char)

sai 400 20210123

goy 500 20210524

But i want to fetch data for this date 20210524

Please help? Pass thru query only

i have written like this where cast(trandate as date)='20210524'

Plz help.

Thankin advance

Re: How to convert number to date in pass thru query and fetch data particular date ?

ChrisNZ — Thu, 29 Jul 2021 10:37:59 GMT

If TRANDATE is a string, why not just

where TRANDATE='20210524'

? (simple or double quotes depending on the platform)

Re: How to convert number to date in pass thru query and fetch data particular date ?

MSK4 — Thu, 29 Jul 2021 11:21:01 GMT

Thank you for your answer. But i want t-1 data means yesterday's
Where i have created macro variable like
%let dayminus =%sysfunc(intnx(day,%sysfunc(date() ),-1),yymmddn8.);
It will resolve to 20210728
I have used in where trandate="&dayminus."
But throwing error. Any help

Re: How to convert number to date in pass thru query and fetch data particular date ?

ChrisNZ — Thu, 29 Jul 2021 11:34:39 GMT

Does it throw the error because you use double quotes?

If so, the question has been asked several times here about quoting a macro value with single quotes. Search the forum.

Re: How to convert number to date in pass thru query and fetch data particular date ?

MSK4 — Thu, 29 Jul 2021 12:19:53 GMT

Even i have used single code. but i'm getting zero records 63 columns
For that i have used trim(tran_date)='&dayminus'
if i hardcodes like tran_date='20210728' Im getting 3000 records

Re: How to convert number to date in pass thru query and fetch data particular date ?

MSK4 — Thu, 29 Jul 2021 12:27:00 GMT

Is there any option to covert character number date into numeric in pass thru query?

Re: How to convert number to date in pass thru query and fetch data particular date ?

Kurt_Bremser — Thu, 29 Jul 2021 12:37:39 GMT

I suggest that you first run the query natively in the database.

Once you have the correct code for that, post it here, so we can suggest ways to build it with SAS.

Re: How to convert number to date in pass thru query and fetch data particular date ?

Tom — Thu, 29 Jul 2021 12:58:32 GMT

@MSK4 wrote:
Even i have used single code. but i'm getting zero records 63 columns
For that i have used trim(tran_date)='&dayminus'
if i hardcodes like tran_date='20210728' Im getting 3000 records

I doubt than any of the TRAN_DATE values have an ampersand in them so not surprised that you got zero observations.

Try using %BQUOTE() to allow you to add the single quotes and also have the macro processor replace the macro variable reference with its value.

trim(tran_date)= %bquote('&dayminus')

Why did you include the TRIM() function call? Are you using ORACLE? Is TRAN_DATE actually a DATETIME variable in the Oracle database? Is it a VARCHAR variable and someone has actually stored trailing spaces into the values?

Re: How to convert number to date in pass thru query and fetch data particular date ?

MSK4 — Thu, 29 Jul 2021 13:32:49 GMT

Yes, Im connecting to Oracle using pass thru query. Now I got it.