DATA Step, Macro, Functions and more

Generate Date from Day of Week, Month, and Year

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Generate Date from Day of Week, Month, and Year

[ Edited ]

Hello all, 

 

I am trying to create a date from 3 variables: weekday (1:7 (Sun - Sat)), month (1:12), and year (2012:2013).  For my analyses, I would like to generate a date so that the weekday falls during the 4 week of the month. I've been trying to use CATX function to combine the three variables, then the NWKDOM function to generate the date, so that it falls in the 4th week of the month.  However, I'm struggling with the code.  Can anyone help? 

 

data have: 

weekday          month           year

1                         1               2012

4                         1               2012

6                         2               2012

3                         3               2012

6                         5              2012

7                        12             2012

4                         2              2013

5                         4              2013

2                        11             2013

 

data want:

01/22/2012

01/25/2012

02/24/2012

03/20/2012...

...

...

11/18/2013

 

Thank you in advance.

 

 

 

 


Accepted Solutions
Solution
‎01-23-2018 03:20 PM
Super User
Posts: 8,220

Re: Generate Date from Day of Week, Month, and Year

I think you are looking for something like the following:

data want;
  set have;
  format date mmddyy10.;
  date=intnx('week',mdy(month,1,year),3,'b')+weekday-1;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Respected Advisor
Posts: 3,288

Re: Generate Date from Day of Week, Month, and Year

You want to use the MDY function, as well as the proper DATE format to make the result actually look like 01/22/2012. It shouldn't be too hard to figure out how to get the 4th week of the month (hint: INTNX function). CATX isn't the way to do this.

--
Paige Miller
Solution
‎01-23-2018 03:20 PM
Super User
Posts: 8,220

Re: Generate Date from Day of Week, Month, and Year

I think you are looking for something like the following:

data want;
  set have;
  format date mmddyy10.;
  date=intnx('week',mdy(month,1,year),3,'b')+weekday-1;
run;

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 44

Re: Generate Date from Day of Week, Month, and Year

Thanks Art, that worked perfectly. 

 

One additional question -- Instead of the date falling during the 4th week of the month, I would like to generate it for the last 7 days of each month. This could be in the 4th or 5th or 4th-5th week of the month. Would this code be more complicated or as simple?

 

Thank you 

Super User
Posts: 8,220

Re: Generate Date from Day of Week, Month, and Year

Not sure what you're asking. It would help if you provide an example like you did for your original question.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 8,220

Re: Generate Date from Day of Week, Month, and Year

With regard to your new question, are you just looking for something like:

data want;
  set have;
  format date mmddyy10.;
  date=NWKDOM(5, weekday, month, year);
run;

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 44

Re: Generate Date from Day of Week, Month, and Year

Thank you for the replies. The below code did not work. As you asked, here is a sample of what I'm going for:

 

Data have:

weekday    month      year

6                 6              2012

2                 9              2013

7                 10            2013

6                 8              2012

3                 5              2012

 

Output from your 1st code (4th week of the month):

06/22/2012

09/23/2013

10/26/2013

08/24/2012

05/22/2012

 

Output from below code (NWKDOM(5, weekday, month, year)):

06/27/2012

09/30/2013 

10/31/2013

08/27/2012

06/27/2012

 

Data want:

06/29/2012

09/30/2013

10/26/2013

08/31/2012

05/29/2012

Super User
Posts: 8,220

Re: Generate Date from Day of Week, Month, and Year

I don't understand how you got the values you said you obtained from the NWDOM function. When I run the code I get the same values that you said you want:

data have;
  input weekday          month           year;
  cards;
6                 6              2012
2                 9              2013
7                 10            2013
6                 8              2012
3                 5              2012
;


data want;
  set have;
  format date mmddyy10.;
  date=NWKDOM(5, weekday, month, year);
run;

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 44

Re: Generate Date from Day of Week, Month, and Year

Sorry, that was my fault, I used discharge data instead of admission last time. It works. Thank you for your assistance today!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 264 views
  • 1 like
  • 3 in conversation