topic Re: Generate Date from Day of Week, Month, and Year in SAS Programming

Generate Date from Day of Week, Month, and Year

eabc0351 — Tue, 23 Jan 2018 17:45:23 GMT

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.

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

PaigeMiller — Tue, 23 Jan 2018 18:06:11 GMT

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.

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

art297 — Tue, 23 Jan 2018 18:15:55 GMT

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

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

eabc0351 — Tue, 23 Jan 2018 18:26:22 GMT

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

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

art297 — Tue, 23 Jan 2018 18:52:25 GMT

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

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

art297 — Tue, 23 Jan 2018 19:03:35 GMT

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

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

eabc0351 — Tue, 23 Jan 2018 19:47:52 GMT

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

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

art297 — Tue, 23 Jan 2018 19:55:58 GMT

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

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

eabc0351 — Tue, 23 Jan 2018 20:20:04 GMT

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