topic Re: Automate query filter by date in SAS Academy for Data Science

Automate query filter by date

BL_463_IDR — Mon, 06 Dec 2021 07:59:50 GMT

Hello,
i want to automate query by date, based on current year (date system on pc/laptop). For information the value of date (OPENING_DATEKEY) that i want to filter is '20210101' (text/string).

for example, the filter date that i currently query like below :

proc sql;
connect to oracle (USER="xxxx" PASSWORD="xxxxx"
PATH="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = xxxx) (PORT = xxxx)))(CONNECT_DATA =(SID = xxxx )))");
create table newccv2.card_0 (COMPRESS=YES) as
select * from connection to oracle
(
select distinct *
from BMDM_COMM.CREDIT_CARD_ACCOUNT
where OPENING_DATEKEY like '2021%'
);
QUIT;

thanks for your attention.

Re: Automate query filter by date

PaigeMiller — Mon, 06 Dec 2021 11:26:06 GMT

What is wrong with the code you show?

Re: Automate query filter by date

BL_463_IDR — Tue, 07 Dec 2021 17:13:36 GMT

i tried using macro like below:

%let date = %SYSFUNC(today(), year4.); /*if current date is 07-12-2021, i want to take the year so the result that i expected is 2021*/

proc sql;
connect to oracle (USER="xxxx" PASSWORD="xxxxx"
PATH="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = xxxx) (PORT = xxxx)))(CONNECT_DATA =(SID = xxxx )))");
create table newccv2.card_0 (COMPRESS=YES) as
select * from connection to oracle
(
select distinct *
from BMDM_COMM.CREDIT_CARD_ACCOUNT
where OPENING_DATEKEY like '&date.%' /* when the macro (%let date) from above, i expected where OPENING_DATEKEY like '2021%*/
);
QUIT;

but it didnt' work, is there another way?

Re: Automate query filter by date

PaigeMiller — Tue, 07 Dec 2021 17:56:20 GMT

For your future benefit: Saying it "didn't work" and providing no additional information doesn't get us anywhere. If there are errors in the log, then show us the log for the run of this macro. If the output is incorrect, then show us the incorrect output (and explain what the correct output should be).

In this particular case, one problem is obvious (although there may be other problems).

'&date.%'

will never work.

You will need double-quotes here to get it to work, and you will need to mask the percent sign. For example:

"&date%nrstr(%%)"

Or if Oracle does not work with double-quotes, then you need

%nrstr(%')&date%nrstr(%%)%nrstr(%')

Re: Automate query filter by date

BL_463_IDR — Thu, 09 Dec 2021 08:50:44 GMT

Dear Page Miller

Thank you for your solution, i used this and it's works as expected.

%nrstr(%')&date%nrstr(%%)%nrstr(%')

i'm sorry for didn't provide additional information for the case, next ill fix it and explain more clear.
Once again, thanks for your solution.

Best Regards