Hi all, I am calculating weeknumber from date calculated using AYMD function. when I try to run the below syntax,
&YEAR_AND_WEEK is displaying 2016-54 instead of 2017-01.


-DEFAULT &REPORT_DATE = '20170106';
-SET &REPORT_DATE = AYMD(&YYMD, -24, 'I8YYMD');
-*-SET &TODAY_DT= &YYMD;
-SET &YEAR = EDIT(&REPORT_DATE,'9999$$$$$$$$') ;
-SET &WEEK = HPART(HDTTM(DATECVT(&REPORT_DATE, I8YYMD, YYMD), 8, 'HYYMDs'), 'WEEK', 'I2');
-SET &WEEK_OF_YEAR=&WEEK+1;
-SET &YEAR_AND_WEEK=&YEAR|| '-' || &WEEK_OF_YEAR;
-SET &WEEKSTART_DT=DATECVT(DATEADD(DATEMOV(DATECVT(&REPORT_DATE, 'I8YYMD','YYMD'), 'BOW'), 'D', -1), 'YYMD', 'A8YYMD');
-SET &WEEKEND_DT=DATECVT(DATEADD(DATEMOV(DATECVT(&REPORT_DATE, 'I8YYMD','YYMD'), 'EOW'), 'D', 1), 'YYMD', 'A8YYMD');
-SET &START_DATE = EDIT(&WEEKSTART_DT,'$$$$$$99')|| '/' ||EDIT(&WEEKSTART_DT,'$$$$99$$$$')|| '/' ||EDIT(&WEEKSTART_DT,'9999$$$$$$$$');
-SET &END_DATE = EDIT(&WEEKEND_DT,'$$$$$$99')|| '/' ||EDIT(&WEEKEND_DT,'$$$$99$$$$')|| '/' ||EDIT(&WEEKEND_DT,'9999$$$$$$$$');
-TYPE &YEAR_AND_WEEK
-TYPE &START_DATE
-TYPE &END_DATE

Can anyone help please.

Thanks in advance.

This message has been edited. Last edited by: Tamra,

...did you notice you're using &YYMD in the second line and not &REPORT_DATE ?

&YYMD = 20170124 today ( for me ).

-/- 24 = 20161231 which -is- week 53 ( depending on what system you use.. but your code gives 53 )

then for some reason you add 1


54 is the anwer.



Perhaps you should first add 7 days -before- the HPART 'week' instead of assuming +1 is always correct.


Good luck,
Dave

make sure you also use the right SET WEEKFIRST

Consider using a date dimension table with all of these values pre-calculated. That way you could just do an ON TABLE HOLD and -READFILE to parameterize all date values.

Hi RadhikaC,
Assumptions:
Week 1 starts with 1 January
Week 2 starts on the first Sunday after 1 January

Calculate the number of days since the beginning of the year and divide by 7 for the number of weeks.
Initial conditions:
If the year starts on a Sunday add 1.
If the year starts on a different day, call it D, then week 1 is until Saturday.
If the day of the date is before D add 2 else add 1.

The code is wrapped in a loop to get random dates.

  
-* File radhikac01.fex
-SET &ECHO=OFF;
-DEFAULT &REPORT_DATE = '20170331', &S='1'
-REPEAT #DDD FOR &I FROM 1 TO 10;
-SET &S=IF &S EQ '1' THEN '-1' ELSE '1';
-SET &RANDAYS=&S * 182 * RDUNIF('D5.4');
-SET &REPORT_DATE = AYMD(&REPORT_DATE, &RANDAYS, 'I8YYMD');
-SET &YEAR = EDIT(&REPORT_DATE,'9999$$$$$$$$');
-SET &YEAR1=&YEAR | '0101';
-SET &DOW=DOWK('&REPORT_DATE.EVAL', 'A4');
-SET &DOW1=DOWK('&YEAR1.EVAL', 'A4');
-*-TYPE &REPORT_DATE &YEAR1 &DOW1
-SET &W=DECODE &DOW(SUN 7 MON 6 TUE 5 WED 4 THU 3 FRI 2 SAT 1);
-SET &W1=DECODE &DOW1(SUN 7 MON 6 TUE 5 WED 4 THU 3 FRI 2 SAT 1);
-SET &DAYS=DATEDIF('&YEAR1.EVAL', '&REPORT_DATE.EVAL', 'D');
-*-TYPE &DAYS
-SET &INIT=IF &DOW1 EQ 'SUN' THEN 1 ELSE IF &DAYS LT &W1 THEN 1 ELSE 
-          IF &W GT &W1 THEN 2 ELSE 1;
-SET &WEEKS=INT(&DAYS / 7) + &INIT ;
-SET &YEAR_AND_WEEK=&YEAR|| '-' || &WEEKS; 
-TYPE &REPORT_DATE &YEAR_AND_WEEK
-#DDD

See here: http://infocenter.informationb.../source/topic166.htm

That gives you the correct year with the week number when January 1st is in week 53 or December 31st is in week 1 and other similar cases where week and year change over on different days.

Remember to set WEEKFIRST to the week numbering scheme that applies to your case, as is mentioned in that link as well.

i had the similar issue. So i ended up using ON SAVE and -READ from it

-* To manually set your start week use the below line (1-7 Sun-Sat)
-*SET WEEKFIRST=1

DEFINE FILE CAR
TODAY/HYYMDs = HDTTM(DATECVT(&MDYY, 'I8MDYY', 'MDYY'), 8,'HYYMDs');
WEEK/I2 = HPART(TODAY,'WEEK','I2');
END

TABLE FILE CAR
PRINT
WEEK
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS WEEKNO
END
-RUN
-READ WEEKNO &WEEKNO.A2

-SET &WEEK = &WEEKNO;

That's a misleading comment you put with the SET WEEKFIRST statement. It sets the day of the week (1 to 7) that the week starts with. You can set it to ISO too.

Additionally, you don't need the TABLE request in your case at all:

SET WEEKFIRST=ISO
-RUN
-SET &WEEK = HPART(HGETC(8, 'HYYMD'), 'WEEK', 'I2');