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;
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.
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;
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
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.
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 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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.