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

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.

 

1 ACCEPTED SOLUTION
7 REPLIES 7
andreas_lds
Jade | Level 19

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

Xinhui
Obsidian | Level 7

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

Patrick
Opal | Level 21

@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;
Astounding
PROC Star

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

ballardw
Super User

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.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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