topic Re: Executing PROC SQL according to weekday in SAS Programming

Executing PROC SQL according to weekday

KP12 — Thu, 14 May 2015 02:33:55 GMT

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.

Re: Executing PROC SQL according to weekday

KP12 — Thu, 14 May 2015 03:44:22 GMT

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;

Re: Executing PROC SQL according to weekday

KP12 — Thu, 14 May 2015 04:34:51 GMT

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

Re: Executing PROC SQL according to weekday

Ksharp — Thu, 14 May 2015 13:48:56 GMT

Make a macro variable .

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

%put &n ;

Xia Keshan

Re: Executing PROC SQL according to weekday

KP12 — Fri, 15 May 2015 01:32:05 GMT

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 (>=)

Re: Executing PROC SQL according to weekday

Ksharp — Fri, 15 May 2015 12:59:04 GMT

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