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.
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
Hi,
You can try this code...not tested in DI...
%let dd=%sysfunc(weekday(%sysfunc(today(),DDMMYY10)));
%put ⅆ
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.