topic Re: Extracting rows based on different weekdays in DI Studio in SAS Data Management

Extracting rows based on different weekdays in DI Studio

TurnTheBacon — Thu, 20 Sep 2012 06:55:57 GMT

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.

Re: Extracting rows based on different weekdays in DI Studio

DanielSantos — Thu, 20 Sep 2012 07:25:45 GMT

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

Re: Extracting rows based on different weekdays in DI Studio

shivas — Thu, 20 Sep 2012 09:42:03 GMT

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