SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How do i use yesterdays date as a variable?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How do i use yesterdays date as a variable?

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.


Accepted Solutions
Solution
‎03-12-2018 05:25 AM
Super User
Posts: 23,295

Re: How do i use yesterdays date as a variable?

[ Edited ]
Posted in reply to woodie2905

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.


 

View solution in original post


All Replies
Solution
‎03-12-2018 05:25 AM
Super User
Posts: 23,295

Re: How do i use yesterdays date as a variable?

[ Edited ]
Posted in reply to woodie2905

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.


 

New Contributor
Posts: 2

Re: How do i use yesterdays date as a variable?

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.

 

Super User
Posts: 23,295

Re: How do i use yesterdays date as a variable?

Posted in reply to woodie2905

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

 

 

Super User
Posts: 3,857

Re: How do i use yesterdays date as a variable?

Posted in reply to woodie2905

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.

Respected Advisor
Posts: 4,674

Re: How do i use yesterdays date as a variable?

Posted in reply to woodie2905

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 232 views
  • 0 likes
  • 4 in conversation