BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lina_K
Calcite | Level 5

I have a column with year (2018-2019) and a column with week number (01-52). Do you know how to create two columns with information on month and quarter based on information about year and week?

 

Example data:

ID

YEAR

WEEK

MONTH

QUARTER

1

2018

1

1(Jan)

1st

1

2018

2

1(Jan)

1st

1

2018

51

12(Dec)

4th

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Try using INTNX() to calculate date.  Then you can use MONTH() and QTR() functions.

data have;
  input ID YEAR WEEK wantMONTH $ wantQUARTER $;
  date = intnx('week',mdy(1,1,year),week-1,'s');
  month=month(date);
  quarter=qtr(date);
  format date yymmdd10.;
cards;
1 2018 1 1(Jan) 1st
1 2018 2 1(Jan) 1st
1 2018 51 12(Dec) 4th
;
                              want       want
Obs    ID    YEAR    WEEK     MONTH     QUARTER          date    month    quarter

 1      1    2018      1     1(Jan)       1st      2018-01-01       1        1
 2      1    2018      2     1(Jan)       1st      2018-01-08       1        1
 3      1    2018     51     12(Dec)      4th      2018-12-17      12        4

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

Try using INTNX() to calculate date.  Then you can use MONTH() and QTR() functions.

data have;
  input ID YEAR WEEK wantMONTH $ wantQUARTER $;
  date = intnx('week',mdy(1,1,year),week-1,'s');
  month=month(date);
  quarter=qtr(date);
  format date yymmdd10.;
cards;
1 2018 1 1(Jan) 1st
1 2018 2 1(Jan) 1st
1 2018 51 12(Dec) 4th
;
                              want       want
Obs    ID    YEAR    WEEK     MONTH     QUARTER          date    month    quarter

 1      1    2018      1     1(Jan)       1st      2018-01-01       1        1
 2      1    2018      2     1(Jan)       1st      2018-01-08       1        1
 3      1    2018     51     12(Dec)      4th      2018-12-17      12        4
Lina_K
Calcite | Level 5
Thank you Tom! It works perfectly.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 732 views
  • 0 likes
  • 2 in conversation