If Statement - Dates

[Jyuen204]

I have a piece of code where in need to validate a day of the week and use a certain date in the query.
If the today is tuesday I want to pull RUN_DATE_MON_SAS date. if day is wed, thur, fri, sat, i wan it to use RUN_DATE_SAS.

Thank you!

Data DATES;
Format RUN_DATE
EXP_DATE
date9.;

RUN_DATE = today()-1;
RUN_DATE_MON = today()-3;
EXP_DATE = today()+1;
RUN_DATE_SAS = "'" || put(run_date, date9.) || "'d";
RUN_DATE_MON_SAS = "'" || put(run_date_mon, date9.) || "'d";

Call Symput('RUN_DATE',REPORT_DATE);

Call Symput('EXP_DATE',EXP_DATE);
Call Symput('RUN_DATE_SAS',RUN_DATE_SAS);
Call Symput('RUN_DATE_MON_SAS',RUN_DATE_MON_SAS);

proc sql;
select * from TABLE1
where A.SOENTD >= &RUN_DATE_MON_SAS;
run;

[Kurt_Bremser]

What about Sunday and Monday?

[Jyuen204]

For my purposes there is no data refreshed on those particular days.

[Kurt_Bremser]

You need to account for those days in your code, because, and I can promise you that, someday someone will run that program on a Sunday or Monday. The least you need to do is a program abort.

[Kurt_Bremser]

data _null_;
select (weekday(today()));
  when (3) run_date = today() - 3; /* Tuesday */
  when (4,5,6,7) run_date = today() - 1; /* Wednesday to Saturday */
  otherwise abort abend 5; /* Sunday, Monday */
end;
call symputx('run_date',run_date);
run;

proc sql;
select * from TABLE1
where A.SOENTD >= &run_date.;
run;

[Jyuen204]

I need to format the RUN_DATE to a teradata format as I am using a passthrough. My apologies I forgot to include that. In my original code I had accounted for this in the coding using "'" || put(run_date, date9.) || "'d";

would I just add this line within your Data section?
RUN_DATE = "'" || put(run_date, date9.) || "'d";

[Kurt_Bremser]

Database systems usually need a YYYY-MM-DD format for date literals. The only system I know that uses the DATE9 is SAS.

See https://docs.teradata.com/reader/S0Fw2AVH8ff3MDA0wDOHlQ/zzj0~rymqnV6H24CtsCiOw 

[Jyuen204]

Querying Via a Teradata passthrough al I need to format the date a bit differently

[Kurt_Bremser]

Well, let's start with the usual:

how does a successful pass-through with a non-macro literal date look?

From that, macro coding can be developed.

[Jyuen204]

My bad, I am querying an Oracle db.

Select * from TABLE
where SOENTD >= '17Oct2020'd

that is if I was using a hardcoded date.
Otherwise I'm currently using this to define the dates

RUN_DATE = today()-1;
RUN_DATE_MON = today()-3;
RUN_DATE_SAS = "'" || put(run_date, date9.) || "'d";
RUN_DATE_MON_SAS = "'" || put(run_date_mon, date9.) || "'d";

[Kurt_Bremser]

This

Select * from TABLE
where SOENTD >= '17Oct2020'd

looks like pure SAS code to me, in Oracle passthrough you would need to use the TO_DATE function:

TO_DATE('02-OCT-02', 'DD-MON-YY')

or a DATE cast:

DATE '2002-10-03'

See https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm 

Please post the complete code of the passthrough

execute ( /* explicit pass-through code */ ) by oracle;

If you don't do explicit pass-through (which means that SAS translates values for you), you do not need a formatted date value. See Maxim 28.

[Jyuen204]

It's not a passthrough. I have a library connecting me to an Oracle DB. I set the libname (as below) but I am Unable to query using a Date9 formatted date and the only way I was able to query the date was using SOENTD >= '17Oct2020'd, That's why I needed to set the dates up front using RUN_DATE_SAS = "'" || put(run_date, date9.) || "'d";

libname prd4 oracle path= BI_MTS_PRD4 schema=prdedw user=&useredw password=&passedw dbcommit=1000;

[Kurt_Bremser]

---

@Jyuen204 wrote:
 That's why I needed to set the dates up front using RUN_DATE_SAS = "'" || put(run_date, date9.) || "'d";

---

No, you don't. In SAS code, the raw value is sufficient. TRY IT:

%let date=%sysfunc(today());

proc sql;
create table test as
  select
    today() as date1,
    '21oct2020'd as date2,
    &date as date3
  from sashelp.class
  where name='Alfred'
;
quit;

proc print data=test;
run;

Result:

Beob.	date1	date2	date3
1	22209	22209	22209

[Kurt_Bremser]

And please make up your mind before posting.

Quote from your earlier post:

I need to format the RUN_DATE to a teradata format as I am using a passthrough.

And now, we're at no pass-through to Oracle.

[Jyuen204]

And I see that it should be the same. Howe4ver when I run the 2 queries. the query with the '20oct2020'd works and i get results. When I use &run_date_test, i get no results. Stumped


data _null_;
select (weekday(today()));
when (3) run_date_test = today() - 3; /* Tuesday */
when (4,5,6,7) run_date_test = today() - 1; /* Wednesday to Saturday */
otherwise abort abend 5; /* Sunday, Monday */
end;
call symputx('run_date_test',run_date_test);
run;

proc sql;
select &run_date_test as date2,
'20oct2020'd as date3
from sashelp.class
where name='Alfred';
quit;

proc sql;
select * from PRD4.WLNSOTRX_EDWT (obs = 10)
where SOENTD = &run_date_test;
run;

proc sql;
select * from PRD4.WLNSOTRX_EDWT (obs = 10)
where SOENTD = '20oct2020'd;
run;