How do i use yesterdays date as a variable?

[woodie2905]

Hello,

I have little to no experience using SAS tool, but need to modify some prewritten code to utilise my employers automation capabilities with the code.

Each morning I am required to produce a sample file and I am hoping to automate this process. There are two variables declared at the start of the SAS code:

rsubmit;

%let MinTxnDate='05MAR2018';

%let MaxExclusiveTxnDate='06MAR2018';

Further down the code proc sql is used to set filter:

select * from COMPANY.TABLE with (nolock)

where transactiondate >= &MinTxnDate.

and transactiondate < &MaxExclusiveTxnDate.

-- As things stand, I have to modify the dates each morning. The MinTxnDate should be yesterday, and Max today. Given that I want to automate this code, how would I set the MinTxnDate and Maxdate to the dates required?

Played around with eval sysdates but not having much luck, but as I say I have never used SAS before this.

[Reeza · Accepted solution]

That code wouldn't work as posted, the dates wouldn't be interpreted correctly, they're being used as character values.

Here's how you could automate it to replicate what you have, assuming you missed something in the post and your code does actually work as expected. Note that I'm not saying it won't work, just not give you what you expect because it's treating the dates as characters not dates.

Use the TODAY() function to return the date and then date9. to format the date. 

%let MaxExclusiveTxnDate=%sysfunc(today(), date9.);
%let MinTxnDate=%sysfunc(putn(%sysevalf(%sysfunc(today()) - 1), date9.));


%put &MinTxnDate.;
%put &MaxExclusiveTxnDate;

---

@woodie2905 wrote:

Hello,

I have little to no experience using SAS tool, but need to modify some prewritten code to utilise my employers automation capabilities with the code.

Each morning I am required to produce a sample file and I am hoping to automate this process. There are two variables declared at the start of the SAS code:

rsubmit;

%let MinTxnDate='05MAR2018';

%let MaxExclusiveTxnDate='06MAR2018';

Further down the code proc sql is used to set filter:

select * from COMPANY.TABLE with (nolock)

where transactiondate >= &MinTxnDate.

and transactiondate < &MaxExclusiveTxnDate.

-- As things stand, I have to modify the dates each morning. The MinTxnDate should be yesterday, and Max today. Given that I want to automate this code, how would I set the MinTxnDate and Maxdate to the dates required?

Played around with eval sysdates but not having much luck, but as I say I have never used SAS before this.

---

[woodie2905]

Hello,

From the top to where the min/max date is referenced, the code looks like this. There's every chance that the date fields are text in the original table, unless you can spot something to suggest otherwise - as I said I'm just trying to interpret someone elses code and tables so can't be sure. Once I am back on LAN tomorrow I will test your suggestion, but if you see anything else in what I've provided below please let me know?

Many thanks

rsubmit;

%let MinTxnDate='03MAR2018'

%let MaxExclusiveTxnDate='04MAR2018'

endrsubmit;

rsubmit;

%macro passthru_ACID;

connect to odbc as ACID(datasrc=ACID);

%mend;

%macro passthru_fcd;

connect to odbc as FCD(datasrc=FCD DBMAX_TEXT=32767);

%mend;

%macro templib_fcd;

LIBNAME FCDTMP ODBC CONNECTION=SHARED READBUFF=500 CURSOR_TYPE=FORWARD_ONLY

DBCONINIT="set DATEFORMAT ymd" DATAsrc=FCD DBMAX_TEXT=32767 SCHEMA=dbo;

%mend;

endrsubmit;

rsubmit;

proc sql noprint;

%passthru_ACID;

create table ATMTxns as select * from connection to ACID (

select * from Reporting.ATMTransactions with (nolock)

where transactiondate >= &MinTxnDate.

and transactiondate < &MaxExclusiveTxnDate.

[Reeza]

---

@woodie2905 wrote:

Hello,

From the top to where the min/max date is referenced, the code looks like this. There's every chance that the date fields are text in the original table

---

Given that it's pass through SQL it could be correct, but I would be checking the logic. Note that it may not give you any syntax error, but the logic would be flawed, since it would be an alphabetical comparison, not numeric.

[SASKiwi]

Where is the table COMPANY.TABLE stored? Is it in another database outside of SAS or in a SAS data library? We can't give provide an answer without knowing this.

[Patrick]

Below code will create and populate the two macro variables exactly as posted in your code.

data _null_;
  call symput('MinTxnDate',"'"||put(today()-1,date9.)||"'");
  call symput('MaxExclusiveTxnDate',"'"||put(today(),date9.)||"'");
  stop;
run;