Extracting rows based on different weekdays in DI Studio

[TurnTheBacon]

I'd appreciate good advice on a problem I'm faced with in DI Studio.

My job includes a source table and an extract transformation. The source table has a datetime variable called "Last_Changed". If today is Monday, the Extract must get all rows where the Last_Changed weekday is between Fri-Sun the previous week. If today to Tue-Fri, the Extract must get all rows from yesterday. I've figured out some of the code:

If weekday(&sysdate"d) = 2 (Monday), extract all rows where "Last_Changed" between "&sysdate"d-3 and "&sysdate"d-1 (previous Friday-Sunday). Else, if weekday(&sysdate"d) in (3,4,5,6) (Tuesday-Friday), extract all rows where "&sysdate"d = today()-1.

My problem is that I can't quite figure out how to set this up in the Extract precode and where-statement.

Thanks for your time.

[DanielSantos]

I guess you already figured a valid where expression

where:

(weekday("&sysdate"d) = 2 and ("&sysdate"d-1) <= LAST_CHANGED between <= ("&sysdate"d-3)) or

(weekday("&sysdate"d) in (3,4,5,6) and LAST_CHANGED = "&sysdate"d-1)

This should extract everything you need.

If LAST_CHANGED is in a date/time form, just apply the DATEPART function to extract the Date portion of it, as the "&SYSDATE" var is represented with a Date only format (DATE9.)

More on DATEPART function here:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245883.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[shivas]

Hi,

You can try this code...not tested in DI...

%let dd=%sysfunc(weekday(%sysfunc(today(),DDMMYY10)));

%put &dd;

options minoperator;

%macro test/mindelimiter=',';

%if &dd in 3,4,5,6 %then %do;

data want;

set &syslast;

where date between "&sysdate"d-3 and "&sysdate"d-1 ;

run;

%end;

%else %if &dd =2 %then %do;

data want;

set &Syslast;

where date between "&sysdate"d and "&sysdate"d-1 ;

run;

%end;

%mend test;

%test;

Thanks,

Shiva