BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
woodie2905
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
Reeza
Super User

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
Calcite | Level 5

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
Super User

@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
PROC Star

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 9352 views
  • 1 like
  • 4 in conversation