Compare Week Code format

[Elenatec]

Dear all,

Hi I having trouble to query or find range of date using Week code format, inside the dataset a column formatted with Weeku5.

when I use proc sql or data step it won't match with string variable query any idea I able to the date from and until to variables?

for example:

Today week code will be 13W29, I wanted to search last year week date range 12W29

every year same week but the date range will be different. and I have a table holding all week range just need to search back the old data.

Another issue about last year 2012 52 week and cross year 31 dec will be 53 week frist day and 2013 1st jan will be 1st week. how to encounter this issue?

Thank you,

Jesh

[Vince28_Statcan]

Depending on what further processing you want to do and your constraints on how you define the same week from year to year, you can use the week function with by-group processing.

Since SQL allows for functions to be applied to variables for the group by statement, you would be able to do a query with

group by week(weekcode)

Since you use weeku5. format, your dates format use sunday as the first day of the week and so does the default second parameter to the week() function so you won't need to change it. You can't quite circumvent the issue of 53 weeks a year as it's based on the format choice and thus is consistent with itself. You would have to be more precise as to how you'd want to handle the 53rd week for us to help with a custom solution.

Week5. format as with all dates formats in SAS are stored as numeric variable. If you wish to query with strings, you need tell SAS to read that string as a date and convert it to the appropriate numeric value e.g.

where weekcode="13W29"d;

or

where weekcode=input("13W29", week5.);

You could also ask sas to convert the numeric value to a string with (where put(weekcode, week5.)="13W29") for comparison but I believe it's better practice to do the opposite as then you can apply functions and arithmetic to the dates.

If you need further details or examples, please provide the same of your variables and an example of a query you wish to achieve.

Vincent

[Elenatec]

Thanks for the reply Vince,

Currently I have a manual keyin time table to justify week code every year  due to the background of the businese which some department use week number differently such as friday to thrusday as a week or saturday to friday and so on. SAS have it own calculation as well. I am dealing with automation how to code or manage time or weekcode for future purpose? ( such as creating a new code to generate further? risk about this table?)

about the code thanks but weeku5. can't use '13W29'd to search but the last day of the week in SAS calculation. then input work fine.

Jesh

[jakarman]

SAS(R) 9.4 Language Reference: Concepts (About Date and Time Intervals) is containing a lot.
IS8601 is giving a standard to the weeknumbers. With  INTCK and INTNX functions you should be able to do all type of calculations.

Remember that week 53 and the 29 february are not occuring every year. You have to deal with that.

Before quering a DBMS it should be handy to have setup common used week/date indications/intervals.  (last week last month etc) using the normal date, date-time conventions as macro-vars.  last week, last month are well defined related to you (simulated) date.

When it comes to calculating working-days  it is possible to add a user customized table for that. (not all holidays are equal).
This is all part of date/time calculations. In reality a very dificult enviroment altough it has the feeling of being simple.