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.
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;
@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
@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.
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
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.