BookmarkSubscribeRSS Feed
dwhsrinath
Calcite | Level 5

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;

year to last week.JPG

thanks

Sri

2 REPLIES 2
LinusH
Tourmaline | Level 20

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

Data never sleeps
ballardw
Super User

Again with consideration of fiscal and calendar year something like

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

may be close to what you're looking for.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1535 views
  • 6 likes
  • 3 in conversation