topic Re: Help on getting year-to-date function in SAS Procedures

Help on getting year-to-date function

dwhsrinath — Mon, 10 Jun 2013 15:10:49 GMT

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

(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

Re: Help on getting year-to-date function

LinusH — Mon, 10 Jun 2013 15:27:07 GMT

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#p0syn64amroombn14vrdzksh459w.htm

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

Re: Help on getting year-to-date function

ballardw — Mon, 10 Jun 2013 20:51:45 GMT

Again with consideration of fiscal and calendar year something like

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

may be close to what you're looking for.