Help on getting year-to-date function

[dwhsrinath]

Hi

In the below table i want to write an sql to get year-to-last week data dynamically so whenever i run the below query it should give me the data starting from financial week 1 to last week. In the below eg if i run today(10th June 13) it should give me data starting from week 1 to week 12.

i am using the following query to connect to fact table

rsubmit;

proc sql;

create table RDW1

as

(select

      a1.WK_IDNT,

      sum(a1.F_SLS_AMT)      as SALES

from RDW.SLS_SBC_LW_DM a1,

      RDW.TIME_WK_DM    a2,

    where a1.WK_IDNT=a2.WK_IDNT and

    ??????????????????

group by a1.WK_IDNT

);

quit;

run;

endrsubmit;

thanks

Sri

[LinusH]

If your financial year is the same as fiscal year, you could dig into the intnx function, which helps you calculate date intervals dynamically. Idf the standard intervals doesn't fit, it even lets you use an imported calendar...

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p0syn64amro...

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p10v3sa3i4k...

[ballardw]

Again with consideration of fiscal and calendar year something like

week(yourdatevariable) = (week(today()) - 1)

may be close to what you're looking for.