topic Re: I'm looking for a function that can tell me what week in the month a given data date is from. in SAS Data Management

I'm looking for a function that can tell me what week in the month a given data date is from.

Xinhui — Wed, 25 Oct 2017 06:17:09 GMT

I'm looking for a function that can tell me what week in the month a given data date is from.

I have weekly data that spans for two years. The data begins from the third week of May 2015. Now, there are some unbalanced structures of duplicate data. What I want to do is to keep all of the duplicate dates four weeks following the third week of May.

However, there are instances in which four weeks ahead of that initial date will end in the same month. I want to account for this by creating an exception where, if this is true, it will use five weeks ahead instead of the usual four.

I know I can use an if statement to accomplish this, but I am unsure how to do this.

I posted my SAS data file. The variable I need to deal with in this file is called _datadate. The problem happens in the following example:

10/02/2015

four weeks later:

10/30/2015

This displays my problem. It shouldn't land in the same month. I would want it to go to 11/06/2015.

Thank you and have a good day.

Re: I'm looking for a function that can tell me what week in the month a given data date is from.

andreas_lds — Wed, 25 Oct 2017 07:03:52 GMT

Sorry, but i don't understand the problem. Please post sample data as data-step.

Re: I'm looking for a function that can tell me what week in the month a given data date is from.

Xinhui — Wed, 25 Oct 2017 07:33:41 GMT

I hope this sample can make you understand what I thought.

Re: I'm looking for a function that can tell me what week in the month a given data date is from.

Xinhui — Wed, 25 Oct 2017 07:37:08 GMT

Re: I'm looking for a function that can tell me what week in the month a given data date is from.

Kurt_Bremser — Wed, 25 Oct 2017 07:50:35 GMT

Excel files are USELESS(!) as example data. Excel files are unstructured dungheaps of garbage. No attributes, nothing.

Quote from @andreas_lds' post: "Please post sample data as data-step."

Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert your dataset to a data step, or write your own.

How to post code is found here: https://communities.sas.com/t5/help/faqpage/faq-category-id/posting#posting

Re: I'm looking for a function that can tell me what week in the month a given data date is from.

Astounding — Wed, 25 Oct 2017 07:53:58 GMT

The DAY function returns the day of the month (1 through 31). That should support the calculations you describe.

Re: I'm looking for a function that can tell me what week in the month a given data date is from.

Patrick — Wed, 25 Oct 2017 10:22:58 GMT

@Xinhui

Looking at your sample data (the column with the date you want) doesn't really marry up with your explanation of the logic you're after. At least I can't make sense of it. For this reason below code won't be the solution for you. May be it gives you at least some ideas.

data sample;
  infile datalines truncover dlm=' ';
  input (dt_have dt_tst) (:mmddyy10.);
  format dt_have dt_tst dt_want date9.;

  dt_want=intnx('week',dt_have,4,'s');
  if intck('month',dt_have,dt_want)=0 then dt_want=intnx('week',dt_have,5,'s');
  datalines;
05/15/2015 05/15/2015
05/15/2015 05/15/2015
05/15/2015 05/15/2015
05/22/2015 06/12/2015
05/22/2015 06/12/2015
05/29/2015 06/12/2015
05/29/2015 06/12/2015
05/29/2015 07/10/2015
05/29/2015 07/10/2015
05/29/2015 08/07/2015
06/05/2015 08/07/2015
06/05/2015 08/07/2015
06/05/2015 09/04/2015
06/12/2015 10/02/2015
06/12/2015 10/02/2015
06/12/2015 11/06/2015
06/12/2015 11/06/2015
;
run;

Re: I'm looking for a function that can tell me what week in the month a given data date is from.

ballardw — Wed, 25 Oct 2017 17:11:38 GMT

Since "Week" is a somewhat slippery topic you need to define exactly all of your rules. You might notice that SAS supplies three different options to the Week function, U, V and W to extract week of year information from dates depending on some common rules.

So how do you determine which week of a month a value may be in?

And when you say "4 weeks" are you making the common mistaken assumption that a month consists of 4 weeks? Since there are 52.143 (approximatel) weeks in most years, then on average (not always useful) there are 4.35 weeks in a month. Or 30/7=4.29 and 31/7=4.43 weeks in given months. Then of course there is Leap Year behavior to consider.