data manipulation question

[art297]

: Longer and with 15,351 records that get assigned the wrong week.

[Ksharp]

Hi Arthur.T ,

I don't understand where is wrong ?

  Saturday, January 10, 1942 1

  Sunday, January 11, 1942 2

Here is a shorter version.

data test;
  format date weekdate29.;
  do date='01JAN1900'd to '31dec2200'd;
    output;
  end;
run;

 
data wantk;
set test;
week=week(date);
if 1 lt weekday(intnx('year',date,0,'b')) lt 5 then week=week+1;
start=intnx('week',date,0,'b');
end=intnx('week',date,0,'e');
start_year=year(start);
end_year=year(end);
 
if start_year ne end_year then do;
  if weekday(mdy(1,1,end_year)) lt 5 then week=1;
   else week=ifn(1 lt weekday(intnx('year',start,0,'b')) lt 5,week(start)+1,week(start));
end;
drop  start  end  start_year  end_year;
run;
 

Xia Keshan

Message was edited by: xia keshan

Message was edited by: xia keshan

[art297]

: Run the comparison, below, between your (wantk) and Patrick's (wantp) code. Patrick's code and mine end up with the same results which we think is what the OP wants:

data test;

  format date weekdate29.;

  do date='01JAN1900'd to '31dec2200'd;

    output;

  end;

run;

data wantp (drop=date_:);

  set test;

  date_shift=intnx('week.4',date+3,0,'b'); /*1st Wed in week*/

  date_fwy=intnx('week.5',intnx('year',date_shift,0,'b'),0,'e'); /*1st Wed in year*/

  week=intck('week',date_fwy,date_shift)+1; /*week number*/

run;

data wantk;

set test;

week=week(date);

if weekday(intnx('year',date,0,'b')) lt 5 then week=week+1;

start=intnx('week',date,0,'b');

end=intnx('week',date,0,'e');

start_year=year(start);

end_year=year(end);

if start_year ne end_year then do;

  if weekday(mdy(1,1,end_year)) lt 5 then week=1;

   else week=ifn(weekday(intnx('year',start,0,'b')) lt 5,week(start)+1,week(start));

end;

drop  start  end  start_year  end_year;

run;

proc compare base=wantk compare=wantp;

run;

[Ksharp]

Hi Arthur,

I know where is wrong . Code has been updated.

data test;
  format date weekdate29.;
  do date='01JAN1900'd to '31dec2200'd;
    output;
  end;
run;
 
data wantp (drop=date_:);
  set test;
  date_shift=intnx('week.4',date+3,0,'b'); /*1st Wed in week*/
  date_fwy=intnx('week.5',intnx('year',date_shift,0,'b'),0,'e'); /*1st Wed in year*/
  week=intck('week',date_fwy,date_shift)+1; /*week number*/
run;
 
data wantk;
set test;
week=week(date);
if 1 lt weekday(intnx('year',date,0,'b')) lt 5 then week=week+1;
start=intnx('week',date,0,'b');
end=intnx('week',date,0,'e');
start_year=year(start);
end_year=year(end);
 
if start_year ne end_year then do;
  if weekday(mdy(1,1,end_year)) lt 5 then week=1;
   else week=ifn(1 lt weekday(intnx('year',start,0,'b')) lt 5,week(start)+1,week(start));
end;
drop  start  end  start_year  end_year;
run;
 
proc compare base=wantk compare=wantp;
run;






               Comparison of WORK.WANTK with WORK.WANTP
                                                          (Method=EXACT)

                                                        Data Set Summary

                                   Dataset              Created          Modified  NVar    NObs

                                   WORK.WANTK  17NOV14:20:51:24  17NOV14:20:51:24     2  109938
                                   WORK.WANTP  17NOV14:20:51:24  17NOV14:20:51:24     2  109938


                                                        Variables Summary

                                              Number of Variables in Common: 2.


                                                       Observation Summary

                                                  Observation      Base  Compare

                                                  First Obs           1        1
                                                  Last  Obs      109938   109938

                                Number of Observations in Common: 109938.
                                Total Number of Observations Read from WORK.WANTK: 109938.
                                Total Number of Observations Read from WORK.WANTP: 109938.

                                Number of Observations with Some Compared Variables Unequal: 0.
                                Number of Observations with All Compared Variables Equal: 109938.

                                NOTE: No unequal values were found. All values compared are exactly equal.

Xia Keshan

[jakarman]

What is wrong with the weekw. function/format?  It is defined as the weeknumber and I believe according your description of having more workdays in December it will week 53 of that year. http://support.sas.com/resources/papers/proceedings13/026-2013.pdf    seem a matter of description week 1 starts when that is a Thursday in the next year.

Use the W modifier nit the u,v as these are using different codings for weeknumbers.

[art297]

: I'd be interested in seeing the problem solved using the week function. I initially looked at it and decided that it wouldn't be of much help to the OP's problem.

As I've mentioned, methinks that 's solution is as parsimonious as one can achieve.

[jakarman]

My mistake, it the weekv (V) having the correct week number version.  SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition  (it is not clear until reading and testing). I have done this manual in V6 for setting period as macrovars goal shifting time periods with weeks. the is8601 implementation was not there in SAS those days

The test:

57       

58         options ls=150;

59         data datecnv;

60           set dates ;

61           datefr=intnx('weekv',datetst,0,'B');

62           datelr=intnx('weekv',datetst,0,'E' );

63           cyear=Year(datetst);

64           put "dateno:" datetst "  " datetst date. "  dateisowk:" datetst weekv. "  date wu" datetst weeku.

65           " -- year: " cyear " -- period begin:" datefr date. " end: " datelr date.

66           ".. formatted short " datetst weekv6.  ;

67          run;

dateno:18259   28DEC09  dateisowk:2009-W53-01  date wu2009-W52-02 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18260   29DEC09  dateisowk:2009-W53-02  date wu2009-W52-03 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18261   30DEC09  dateisowk:2009-W53-03  date wu2009-W52-04 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18262   31DEC09  dateisowk:2009-W53-04  date wu2009-W52-05 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18263   01JAN10  dateisowk:2009-W53-05  date wu2010-W00-06 -- year: 2010  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18264   02JAN10  dateisowk:2009-W53-06  date wu2010-W00-07 -- year: 2010  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18265   03JAN10  dateisowk:2009-W53-07  date wu2010-W01-01 -- year: 2010  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18266   04JAN10  dateisowk:2010-W01-01  date wu2010-W01-02 -- year: 2010  -- period begin:04JAN10 end: 10JAN10.. formatted short 10W01

dateno:18267   05JAN10  dateisowk:2010-W01-02  date wu2010-W01-03 -- year: 2010  -- period begin:04JAN10 end: 10JAN10.. formatted short 10W01

dateno:18268   06JAN10  dateisowk:2010-W01-03  date wu2010-W01-04 -- year: 2010  -- period begin:04JAN10 end: 10JAN10.. formatted short 10W01

dateno:18624   28DEC10  dateisowk:2010-W52-02  date wu2010-W52-03 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18625   29DEC10  dateisowk:2010-W52-03  date wu2010-W52-04 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18626   30DEC10  dateisowk:2010-W52-04  date wu2010-W52-05 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18627   31DEC10  dateisowk:2010-W52-05  date wu2010-W52-06 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18628   01JAN11  dateisowk:2010-W52-06  date wu2011-W00-07 -- year: 2011  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18629   02JAN11  dateisowk:2010-W52-07  date wu2011-W01-01 -- year: 2011  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18630   03JAN11  dateisowk:2011-W01-01  date wu2011-W01-02 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18631   04JAN11  dateisowk:2011-W01-02  date wu2011-W01-03 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18632   05JAN11  dateisowk:2011-W01-03  date wu2011-W01-04 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18633   06JAN11  dateisowk:2011-W01-04  date wu2011-W01-05 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18989   28DEC11  dateisowk:2011-W52-03  date wu2011-W52-04 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18990   29DEC11  dateisowk:2011-W52-04  date wu2011-W52-05 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18991   30DEC11  dateisowk:2011-W52-05  date wu2011-W52-06 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18992   31DEC11  dateisowk:2011-W52-06  date wu2011-W52-07 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18993   01JAN12  dateisowk:2011-W52-07  date wu2012-W01-01 -- year: 2012  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18994   02JAN12  dateisowk:2012-W01-01  date wu2012-W01-02 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18995   03JAN12  dateisowk:2012-W01-02  date wu2012-W01-03 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18996   04JAN12  dateisowk:2012-W01-03  date wu2012-W01-04 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18997   05JAN12  dateisowk:2012-W01-04  date wu2012-W01-05 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18998   06JAN12  dateisowk:2012-W01-05  date wu2012-W01-06 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:19355   28DEC12  dateisowk:2012-W52-05  date wu2012-W52-06 -- year: 2012  -- period begin:24DEC12 end: 30DEC12.. formatted short 12W52

dateno:19356   29DEC12  dateisowk:2012-W52-06  date wu2012-W52-07 -- year: 2012  -- period begin:24DEC12 end: 30DEC12.. formatted short 12W52

dateno:19357   30DEC12  dateisowk:2012-W52-07  date wu2012-W53-01 -- year: 2012  -- period begin:24DEC12 end: 30DEC12.. formatted short 12W52

dateno:19358   31DEC12  dateisowk:2013-W01-01  date wu2012-W53-02 -- year: 2012  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19359   01JAN13  dateisowk:2013-W01-02  date wu2013-W00-03 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19360   02JAN13  dateisowk:2013-W01-03  date wu2013-W00-04 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19361   03JAN13  dateisowk:2013-W01-04  date wu2013-W00-05 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19362   04JAN13  dateisowk:2013-W01-05  date wu2013-W00-06 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19363   05JAN13  dateisowk:2013-W01-06  date wu2013-W00-07 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19364   06JAN13  dateisowk:2013-W01-07  date wu2013-W01-01 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19720   28DEC13  dateisowk:2013-W52-06  date wu2013-W51-07 -- year: 2013  -- period begin:23DEC13 end: 29DEC13.. formatted short 13W52

dateno:19721   29DEC13  dateisowk:2013-W52-07  date wu2013-W52-01 -- year: 2013  -- period begin:23DEC13 end: 29DEC13.. formatted short 13W52

dateno:19722   30DEC13  dateisowk:2014-W01-01  date wu2013-W52-02 -- year: 2013  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19723   31DEC13  dateisowk:2014-W01-02  date wu2013-W52-03 -- year: 2013  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19724   01JAN14  dateisowk:2014-W01-03  date wu2014-W00-04 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19725   02JAN14  dateisowk:2014-W01-04  date wu2014-W00-05 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19726   03JAN14  dateisowk:2014-W01-05  date wu2014-W00-06 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19727   04JAN14  dateisowk:2014-W01-06  date wu2014-W00-07 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19728   05JAN14  dateisowk:2014-W01-07  date wu2014-W01-01 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19729   06JAN14  dateisowk:2014-W02-01  date wu2014-W01-02 -- year: 2014  -- period begin:06JAN14 end: 12JAN14.. formatted short 14W02

dateno:20085   28DEC14  dateisowk:2014-W52-07  date wu2014-W52-01 -- year: 2014  -- period begin:22DEC14 end: 28DEC14.. formatted short 14W52

dateno:20086   29DEC14  dateisowk:2015-W01-01  date wu2014-W52-02 -- year: 2014  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20087   30DEC14  dateisowk:2015-W01-02  date wu2014-W52-03 -- year: 2014  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20088   31DEC14  dateisowk:2015-W01-03  date wu2014-W52-04 -- year: 2014  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20089   01JAN15  dateisowk:2015-W01-04  date wu2015-W00-05 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20090   02JAN15  dateisowk:2015-W01-05  date wu2015-W00-06 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20091   03JAN15  dateisowk:2015-W01-06  date wu2015-W00-07 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20092   04JAN15  dateisowk:2015-W01-07  date wu2015-W01-01 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20093   05JAN15  dateisowk:2015-W02-01  date wu2015-W01-02 -- year: 2015  -- period begin:05JAN15 end: 11JAN15.. formatted short 15W02

dateno:20094   06JAN15  dateisowk:2015-W02-02  date wu2015-W01-03 -- year: 2015  -- period begin:05JAN15 end: 11JAN15.. formatted short 15W02

dateno:20450   28DEC15  dateisowk:2015-W53-01  date wu2015-W52-02 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20451   29DEC15  dateisowk:2015-W53-02  date wu2015-W52-03 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20452   30DEC15  dateisowk:2015-W53-03  date wu2015-W52-04 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20453   31DEC15  dateisowk:2015-W53-04  date wu2015-W52-05 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20454   01JAN16  dateisowk:2015-W53-05  date wu2016-W00-06 -- year: 2016  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20455   02JAN16  dateisowk:2015-W53-06  date wu2016-W00-07 -- year: 2016  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20456   03JAN16  dateisowk:2015-W53-07  date wu2016-W01-01 -- year: 2016  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20457   04JAN16  dateisowk:2016-W01-01  date wu2016-W01-02 -- year: 2016  -- period begin:04JAN16 end: 10JAN16.. formatted short 16W01

dateno:20458   05JAN16  dateisowk:2016-W01-02  date wu2016-W01-03 -- year: 2016  -- period begin:04JAN16 end: 10JAN16.. formatted short 16W01

dateno:20459   06JAN16  dateisowk:2016-W01-03  date wu2016-W01-04 -- year: 2016  -- period begin:04JAN16 end: 10JAN16.. formatted short 16W01

dateno:20816   28DEC16  dateisowk:2016-W52-03  date wu2016-W52-04 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20817   29DEC16  dateisowk:2016-W52-04  date wu2016-W52-05 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20818   30DEC16  dateisowk:2016-W52-05  date wu2016-W52-06 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20819   31DEC16  dateisowk:2016-W52-06  date wu2016-W52-07 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20820   01JAN17  dateisowk:2016-W52-07  date wu2017-W01-01 -- year: 2017  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20821   02JAN17  dateisowk:2017-W01-01  date wu2017-W01-02 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20822   03JAN17  dateisowk:2017-W01-02  date wu2017-W01-03 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20823   04JAN17  dateisowk:2017-W01-03  date wu2017-W01-04 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20824   05JAN17  dateisowk:2017-W01-04  date wu2017-W01-05 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20825   06JAN17  dateisowk:2017-W01-05  date wu2017-W01-06 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

NOTE: There were 80 observations read from the data set WORK.DATES.

NOTE: The data set WORK.DATECNV has 80 observations and 4 variables.

NOTE: DATA statement used (Total process time):