How to create week_year between two dates for multiple ID

[Sastech]

Hi All,

I am trying to get a number of week year between two dates. 

I have tried much and unable to get the expecting result. 

Current dataset 

ID	Name	From date	To date
1	A	30-12-2022	15-01-2023
2	B	24-01-2023	06-02-2023

Expecting result dataset

ID	Name	Date	WEEk_year	Month_year
1	A	30-12-2022	22W53	Dec-22
1	A	31-12-2022	22W53	Dec-22
1	A	01-01-2023	23W01	Jan-23
1	A	02-01-2023	23W01	Jan-23
1	A	03-01-2023	23W01	Jan-23
1	A	04-01-2023	23W01	Jan-23
1	A	05-01-2023	23W01	Jan-23
1	A	06-01-2023	23W01	Jan-23
1	A	07-01-2023	23W01	Jan-23
1	A	08-01-2023	23W02	Jan-23
1	A	09-01-2023	23W02	Jan-23
1	A	10-01-2023	23W02	Jan-23
1	A	11-01-2023	23W02	Jan-23
1	A	12-01-2023	23W02	Jan-23
1	A	13-01-2023	23W02	Jan-23
1	A	14-01-2023	23W02	Jan-23
1	A	15-01-2023	23W03	Jan-23
2	B	24-01-2023	23W04	Jan-23
2	B	25-01-2023	23W04	Jan-23
2	B	26-01-2023	23W04	Jan-23
2	B	27-01-2023	23W04	Jan-23
2	B	28-01-2023	23W04	Jan-23
2	B	29-01-2023	23W05	Jan-23
2	B	30-01-2023	23W05	Jan-23
2	B	31-01-2023	23W05	Jan-23
2	B	01-02-2023	23W05	Feb-23
2	B	02-02-2023	23W05	Feb-23
2	B	03-02-2023	23W05	Feb-23
2	B	04-02-2023	23W05	Feb-23
2	B	05-02-2023	23W06	Feb-23
2	B	06-02-2023	23W06	Feb-23

Please help me how to get this result.

@PaigeMiller @Kurt_Bremser @Ksharp @Tom @Patrick @yabwon 

[ballardw]

Can you describe the rule(s) you are using to make those values as Week 53? As in what date is the first day of Week53?

Your rule doesn't apparently match any of the standard week descriptors, U, V or W used with the Week function and formats so you need to provide, possibly a lot, of details.

The U descriptor comes closest for your other values but not for the December ones, possibly because of the year you chose.

It is extremely likely that you do not need three variables. Just assign an appropriate format at time of use.

Please in  the future attempt to provide your starting data at least in the form of a working data step.

This is as close as I can get without some details about week 53 as requested.

data have;
   input ID $ Name $ Fromdate :ddmmyy10. Todate :ddmmyy10.;
   format fromdate todate date9.;
datalines;
1	A	30-12-2022	15-01-2023
2	B	24-01-2023	06-02-2023
;


data want;
   set have;
   do date=fromdate to todate;
      week_year =date;
      month_year=date;

      output;
   end;
   format date ddmmyy10. week_year yyweeku5. month_year monyy6.;
run;
/* if your month_year absolutely must have a dash write a custom format
   and use that instead of the SAS supplied monyy6. format 
*/
proc format ;
picture mon_year
low-high ='%3B-%0y' (datatype=date);
run;

[Sastech]

@ballardw  Hi Sir 

Thank you for your quick help 🙂 

The Week 52 is correct.

For sample data i have created in the excel with the week number formula so that the glitch .

For count of 2 id its working fine and getting expected result.

But For the large number of id's like in the actual dataset more than 1000 ID's are there and getting below shared error.

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.

Please suggest to avoid this error

[ballardw]

---

@Sastech wrote:

@ballardw  Hi Sir 

Thank you for your quick help 🙂 

The Week 52 is correct.

For sample data i have created in the excel with the week number formula so that the glitch .

For count of 2 id its working fine and getting expected result.

But For the large number of id's like in the actual dataset more than 1000 ID's are there and getting below shared error.

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.

Please suggest to avoid this error

---

Your error message implies that either the from date , to date or both are missing in your source data.

Either correct the data or add some thing like

if fromdate and todate then do date=fromdate to todate;

Note the above If will only work if fromdate and todate are also not 1 Jan 1960 (a zero is considered False in such statements and that date is day 0). If that may occur then pick some reasonable date prior to any of your values and use fromdate> <that date> and todate >  <that date>.

Or provide an actual example of the data that fails.

[Tom]

DEFINE how you are counting WEEKs.

You seem to be starting the weeks on either SUNDAY or January First.  We cannot tell which from your example because January 1, 2023 was a Sunday.

But if you were counting from Sunday then 31dec2022 is in week 52.

What do you want to do with 31DEC2021 and 01JAN2022?

Let's convert your listings into dataset (and attach an date format that prints unambiguous values).

data have;
  input ID $ Name $ (Fromdate  Todate) (:ddmmyy.);
  format Fromdate Todate yymmdd10.;
cards;
0 X 31-12-2021 09-01-2022
1 A 30-12-2022 15-01-2023
2 B 24-01-2023 06-02-2023
;


data expect ;
  input ID $ Name $ Date :ddmmyy. WEEk_year $ Month_year $;
cards;
1 A 30-12-2022 22W53 Dec-22
1 A 31-12-2022 22W53 Dec-22
1 A 01-01-2023 23W01 Jan-23
1 A 02-01-2023 23W01 Jan-23
1 A 03-01-2023 23W01 Jan-23
1 A 04-01-2023 23W01 Jan-23
1 A 05-01-2023 23W01 Jan-23
1 A 06-01-2023 23W01 Jan-23
1 A 07-01-2023 23W01 Jan-23
1 A 08-01-2023 23W02 Jan-23
1 A 09-01-2023 23W02 Jan-23
1 A 10-01-2023 23W02 Jan-23
1 A 11-01-2023 23W02 Jan-23
1 A 12-01-2023 23W02 Jan-23
1 A 13-01-2023 23W02 Jan-23
1 A 14-01-2023 23W02 Jan-23
1 A 15-01-2023 23W03 Jan-23
2 B 24-01-2023 23W04 Jan-23
2 B 25-01-2023 23W04 Jan-23
2 B 26-01-2023 23W04 Jan-23
2 B 27-01-2023 23W04 Jan-23
2 B 28-01-2023 23W04 Jan-23
2 B 29-01-2023 23W05 Jan-23
2 B 30-01-2023 23W05 Jan-23
2 B 31-01-2023 23W05 Jan-23
2 B 01-02-2023 23W05 Feb-23
2 B 02-02-2023 23W05 Feb-23
2 B 03-02-2023 23W05 Feb-23
2 B 04-02-2023 23W05 Feb-23
2 B 05-02-2023 23W06 Feb-23
2 B 06-02-2023 23W06 Feb-23
;

Now we can expand you dates to all of the day between from and to.  Let's make multiple copies and the dates and display them using different formats.  And merge on your desired labels.

data want;
  set have;
  do date=fromdate to todate;
    dow = date;
    weeku=date;
    month=date;
    output;
  end;
format date yymmdd10. dow downame. weeku weeku11. month monyy7.;
run;

data want;
  merge expect want;
  by id name date;
  drop fromdate todate;
run;

So what would you want to fill in for the those end of 2021 start of 2022 dates?

                                   WEEk_    Month_
Obs    ID    Name          Date    year      year           dow    weeku            month

  1    0      X      2021-12-31                          Friday    2021-W52-06    DEC2021
  2    0      X      2022-01-01                        Saturday    2022-W00-07    JAN2022
  3    0      X      2022-01-02                          Sunday    2022-W01-01    JAN2022
  4    0      X      2022-01-03                          Monday    2022-W01-02    JAN2022
  5    0      X      2022-01-04                         Tuesday    2022-W01-03    JAN2022
  6    0      X      2022-01-05                       Wednesday    2022-W01-04    JAN2022
  7    0      X      2022-01-06                        Thursday    2022-W01-05    JAN2022
  8    0      X      2022-01-07                          Friday    2022-W01-06    JAN2022
  9    0      X      2022-01-08                        Saturday    2022-W01-07    JAN2022
 10    0      X      2022-01-09                          Sunday    2022-W02-01    JAN2022
 11    1      A      2022-12-30    22W53    Dec-22       Friday    2022-W52-06    DEC2022
 12    1      A      2022-12-31    22W53    Dec-22     Saturday    2022-W52-07    DEC2022
 13    1      A      2023-01-01    23W01    Jan-23       Sunday    2023-W01-01    JAN2023
 14    1      A      2023-01-02    23W01    Jan-23       Monday    2023-W01-02    JAN2023
 15    1      A      2023-01-03    23W01    Jan-23      Tuesday    2023-W01-03    JAN2023
 16    1      A      2023-01-04    23W01    Jan-23    Wednesday    2023-W01-04    JAN2023
 17    1      A      2023-01-05    23W01    Jan-23     Thursday    2023-W01-05    JAN2023
 18    1      A      2023-01-06    23W01    Jan-23       Friday    2023-W01-06    JAN2023
 19    1      A      2023-01-07    23W01    Jan-23     Saturday    2023-W01-07    JAN2023
 20    1      A      2023-01-08    23W02    Jan-23       Sunday    2023-W02-01    JAN2023
 21    1      A      2023-01-09    23W02    Jan-23       Monday    2023-W02-02    JAN2023
 22    1      A      2023-01-10    23W02    Jan-23      Tuesday    2023-W02-03    JAN2023
 23    1      A      2023-01-11    23W02    Jan-23    Wednesday    2023-W02-04    JAN2023
 24    1      A      2023-01-12    23W02    Jan-23     Thursday    2023-W02-05    JAN2023
 25    1      A      2023-01-13    23W02    Jan-23       Friday    2023-W02-06    JAN2023
 26    1      A      2023-01-14    23W02    Jan-23     Saturday    2023-W02-07    JAN2023
 27    1      A      2023-01-15    23W03    Jan-23       Sunday    2023-W03-01    JAN2023
 28    2      B      2023-01-24    23W04    Jan-23      Tuesday    2023-W04-03    JAN2023
 29    2      B      2023-01-25    23W04    Jan-23    Wednesday    2023-W04-04    JAN2023
 30    2      B      2023-01-26    23W04    Jan-23     Thursday    2023-W04-05    JAN2023
 31    2      B      2023-01-27    23W04    Jan-23       Friday    2023-W04-06    JAN2023
 32    2      B      2023-01-28    23W04    Jan-23     Saturday    2023-W04-07    JAN2023
 33    2      B      2023-01-29    23W05    Jan-23       Sunday    2023-W05-01    JAN2023
 34    2      B      2023-01-30    23W05    Jan-23       Monday    2023-W05-02    JAN2023
 35    2      B      2023-01-31    23W05    Jan-23      Tuesday    2023-W05-03    JAN2023
 36    2      B      2023-02-01    23W05    Feb-23    Wednesday    2023-W05-04    FEB2023
 37    2      B      2023-02-02    23W05    Feb-23     Thursday    2023-W05-05    FEB2023
 38    2      B      2023-02-03    23W05    Feb-23       Friday    2023-W05-06    FEB2023
 39    2      B      2023-02-04    23W05    Feb-23     Saturday    2023-W05-07    FEB2023
 40    2      B      2023-02-05    23W06    Feb-23       Sunday    2023-W06-01    FEB2023
 41    2      B      2023-02-06    23W06    Feb-23       Monday    2023-W06-02    FEB2023

[yabwon]

data have;
  input ID $ Name $ (Fromdate  Todate) (:ddmmyy.);
  format Fromdate Todate yymmdd10.;
cards;
1 A 30-12-2022 15-01-2023
2 B 24-01-2023 06-02-2023
;
run;

data want;
  set have;
  do d=fromdate to todate;

    y=mod(year(d),100);
    w=WEEK(d,"U");
    length m $ 3;
    m = put(d,monname.-L);

    WEEk_year = cats(y, "W", w);
    Month_year= cats(m, "-", y);
    output;
  end;
  drop m y w d;
run;
proc print;
run;

How did your get 53?

Bart