DATA Step, Macro, Functions and more

Executing PROC SQL according to weekday

Reply
Contributor
Posts: 24

Executing PROC SQL according to weekday

Hello Everyone,

I have a following code which need to be run according to weekday; the difference is in the WHERE clause of the codes:


if weekday(today()) NE 2 then:

PROC SQL;

create table work.Amount as

SELECT A,B,C

FROM Amount_Month

WHERE DATE_AMT = today()-1;

QUIT;

if weekday(today()) EQ 2 then:

PROC SQL;

create table work.Amount as

SELECT A,B,C

FROM Amount_Month

WHERE DATE_AMT >= today()-3;

QUIT;

How can make this code run, as I tried using it inside data _null_ but I'm getting an error, please help. Thanks in advance.

Contributor
Posts: 24

Re: Executing PROC SQL according to weekday

Tried achieving the task using following code, but again getting an error:

ERROR 180-322: Statement is not valid or it is used out of proper order.

%macro except_monday;

PROC SQL;

create table work.Amount as

SELECT A,B,C

FROM Amount_Month

WHERE DATE_AMT = today()-1;

QUIT;

%mend;

%macro for_monday;

PROC SQL;

create table work.Amount as

SELECT A,B,C

FROM Amount_Month

WHERE DATE_AMT >= today()-3;

QUIT;

%mend;

data _null_;

if (weekday(today()=2)) then %for_monday;

else %except_monday;

run;

Contributor
Posts: 24

Re: Executing PROC SQL according to weekday

Hello everyone,

added call execute in the data step & the code is working fine now:

data _null_;

if weekday(today())=2 then call execute ('%for_monday');

else call execute('%except_monday');

run;


Any further improvements are welcome. Thank you Smiley Happy

Super User
Posts: 10,028

Re: Executing PROC SQL according to weekday

Make a macro variable .

%let n=%sysfunc(ifn(%sysfunc(weekday( %sysfunc(today()) )) NE 2,1,3 )) ;

%put &n ;

Xia Keshan

Contributor
Posts: 24

Re: Executing PROC SQL according to weekday

Hi Xia Keshan,

In your code I believe we can achieve the value of 1 & 3 depending on the weekday, but the actual issue is in the WHERE clause. So for other weekdays the WHERE clause should be an EQUAL TO (=) & for monday it should be GREATER THAN EQUAL TO (>=)

Super User
Posts: 10,028

Re: Executing PROC SQL according to weekday

OH. I missed one condition . Try this one :

%let cond=%sysfunc(ifc(%sysfunc(weekday( %sysfunc(today()) )) NE 2, = today()-1 , >= today()-3 )) ;

%put &cond ;

Xia Keshan

Ask a Question
Discussion stats
  • 5 replies
  • 467 views
  • 1 like
  • 2 in conversation