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

[MSK4]

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

[ChrisNZ]

If TRANDATE is a string, why not just

where TRANDATE='20210524'

?   (simple or double quotes depending on the platform)

[MSK4]

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

[ChrisNZ]

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.

[MSK4]

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

[MSK4]

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

[Kurt_Bremser]

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.

[Tom · Accepted solution]

---

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

[MSK4]

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