SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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

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.

 


Accepted Solutions
Solution
‎10-27-2017 12:41 PM
Super User
Posts: 9,914

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

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-dat... 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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Valued Guide
Posts: 521

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

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

Contributor
Posts: 29

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

Posted in reply to andreas_lds

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

Contributor
Posts: 29

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

Posted in reply to andreas_lds
Solution
‎10-27-2017 12:41 PM
Super User
Posts: 9,914

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

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-dat... 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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,679

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

@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;
Super User
Posts: 6,634

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

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

Super User
Posts: 13,332

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

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.

 

 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 187 views
  • 0 likes
  • 6 in conversation