Joined: 10 Dec 2010 Posts: 96 Location: Massachusetts
Hi,
We are currently working on a conversion from the legacy system to a client server application and we have been asked to provide test data that is future dated. Invoices, orders, inventory, benefits etc. the whole shooting match should look like it would in say 3 months to 10 months or so out. We took the dates ( several different formats ) and with ICETOOL converted them to the 8 byte format:
Code:
Command ===> Scroll ===> CSR
****** ***************************** Top of Data *****************************
000001 20120427
****** **************************** Bottom of Data ***************************
What we are struggling with is that if we add 3 months, or longer timeframe, with the date functions of icetool to the dates we know we have a tight and correct date but are unsure whether this falls on a weekend or an american holiday. If the date does fall on one of the these anamolies we need either to subratct X number of days or add X number of days to ensure the scheduler, online systems and even weekely or monthly batch jobs we run do so in there usual lockstep. This one might be 'out there' but it is worth a new topic if it can be done. Thanks again.
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
Robert Sample wrote:
For weekends, use Zeller's congruence.
For American holidays, you may have to write a program that uses a table of them since there's a lot of irregularity in them.
I agree that a little program to select from a holiday table is wanted. That being the case, the program might as well use LE or language-specific functions to get the weekday also.
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
Frank Yaeger wrote:
Kolusu is our DFSORT "date" expert. He's on vacation today, but I'll ask him to comment on this tomorrow when he gets back.
Adding business calendar functionality might be an interesting DFSORT enhancement. The DFSORT user would have to supply the local business calendar in some sort of standard format that DFSORT would use for various date manipulations. Lots of details to be worked out.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Robert Sample wrote:
For weekends, use Zeller's congruence.
For American holidays, you may have to write a program that uses a table of them since there's a lot of irregularity in them.
You don't have to use Zeller's congruence, as DFSORT has the WEEKDAY function which displays the Day of the week in 3 different formats.
And it is also easy to generate the common American holidays.
madmartinsonxx wrote:
What we are struggling with is that if we add 3 months, or longer timeframe, with the date functions of icetool to the dates we know we have a tight and correct date but are unsure whether this falls on a weekend or an american holiday. If the date does fall on one of the these anamolies we need either to subratct X number of days or add X number of days to ensure the scheduler, online systems and even weekely or monthly batch jobs we run do so in there usual lockstep. This one might be 'out there' but it is worth a new topic if it can be done. Thanks again.
madmartinsonxx,
It can be easily handled in DFSORT. With DFSORT's available Date functions you can validate and generate the common American holidays .
Common Public Holidays in USA
Code:
1. New Years (January 1st)
2. Martin Luther King, Jr Birthday (Third Monday in January)
3. Memorial Day (Last Monday in May)
4. Independence Day (July 4th)
5. Labor Day (First Monday in September)
6. Thanks Giving Day (Fourth Thursday in November)
7. Day After Thanks Giving
8. Christmas (December 25th)
If any of the holidays( New years, Independence Day, Christmas) fall on Saturday, then the previous Friday is declared as holiday. Here exception is new years. If it falls on Saturday, the previous Friday December 31st of prior year is a holiday. ex: January 1st 2011 falls on Saturday and in this case December 31st 2010 is declared as a holiday.
If any of the holidays( New years, Independence Day, Christmas) fall on Sunday, then the following Monday is declared as holiday.
It is easier to generate such holidays using the date functions of DFSORT considering all the scenarios mentioned above.
Now we will use the above symbols to validate the target date after adding the desired number of months or days (in this case 3 months).
Assuming this the input with sample dates
Code:
20120427
20120428
20120429
20120603
2012-04-27 + 3 months = 2012-07-27 which falls on Friday. so no need to increment the date
2012-04-28 + 3 months = 2012-07-28 which falls on Saturday. so increment the date by 2 days to get the next Monday which is 2012-07-30
2012-04-29 + 3 months = 2012-07-29 which falls on Sunday. so increment the date by 1 day to get the next Monday which is 2012-07-30
2012-06-03 + 3 months = 2012-09-03 which falls on Monday, however it is a labor day holiday, so increment the date by 1 day to get the next Tuesday which is 2012-09-04
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
sqlcode1 wrote:
Quote:
From Manual :- NEXTDday can be used to calculate the next specified day of the week for a date field.
Didn't know NEXTDday would stay there if the supplied date is the "day" itself.
Thanks,
I am not sure as to where you got that information but if the supplied date is the "day" itself and you want the NEXTDday, then it does get you to the next weekday.
For ex: today is Monday April 9th 2012 and using NEXTDMON on that date would result in Monday April 16th 2012.
I am not sure as to where you got that information but if the supplied date is the "day" itself and you want the NEXTDday, then it does get you to the next weekday.
I was lost in the way you calculated MLK and TGD. I didn't catch the lines where you subtracted 1 day from 20120101 to calculate MLK but you had used 20121031 while calculating TGD.
Quote:
I just realized that there is a bug in second step where I am validating input file with the holidays. Can anyone pick that bug?
It took me sometime to understand the solution and it will be premature for me to say its a bug so I am going to ask question. In the first step you would have already accounted for Saturday (subtract 1 day) and Sunday (add 1 day) to get to actual holiday but in step 2 you are going by Next Monday and then matching the value with holiday, which "probably" won't work. For example, if year is 2009 and input date is 20090403 the computed date comes to 20090704 which is 4th of July holiday.
I am having too many typos and misunderstandings today so I am not even going to attempt a solution but this solution is a keep for future requirements.
Yes, when the holiday is certain day of certain week, he goes back to end of previous month and then goes to NEXTday(monday or thursday). Later he adds 14 and 21 days depending on holiday. I missed the part where he goes back differently for different holiday.
Like I said, that part is a total misunderstanding on my end.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
sqlcode1 wrote:
It took me sometime to understand the solution and it will be premature for me to say its a bug so I am going to ask question. In the first step you would have already accounted for Saturday (subtract 1 day) and Sunday (add 1 day) to get to actual holiday but in step 2 you are going by Next Monday and then matching the value with holiday, which "probably" won't work. For example, if year is 2009 and input date is 20090403 the computed date comes to 20090704 which is 4th of July holiday.
I am having too many typos and misunderstandings today so I am not even going to attempt a solution but this solution is a keep for future requirements.
Thanks,
Well you are close , as the bug is when the holiday falls on a Friday, the logic would fail. I was simply adding 1 day if it is a holiday. So that would result in a saturday date which is a weekend. Also the same is the case with Thanks giving day. We need to get the NEXT monday for these dates and then validate to check if that monday is a holiday and then another day to it.
The conditions are
Code:
IF COMPUTED_DATE_DAY_OF_WEEK = 'SATURDAY' OR
COMPUTED_DATE_DAY_OF_WEEK = 'SUNDAY' OR
COMPUTED_DATE = 'THANKS GIVING' OR
COMPUTED_DATE = 'DAY AFTER TG')
INCREMENT THE DATE TO NEXT MONDAY
END-IF
IF (COMPUTED_DATE = 'NEW YEARS' OR
COMPUTED_DATE = 'INDEPENDENCE DAY' OR
COMPUTED_DATE = 'CHRISTMAS' OR
COMPUTED_DATE = 'NEW YEARS PREV DAY') AND
COMPUTED_DATE_DAY_OF_WEEK = 'FRIDAY'
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
There was a little confusion with my earlier reply. I didn't notice sqlcode1's, which wasn't there when I started typing but was by the time I finished double-checking.
The problem is, after 1st October, any addition of three months takes you into the following year, yet the holidays set up (only Ney Year's Day and Martin Luther King's birthday are relevant for three months) are the wrong ones to check against.
With concordance I've attempted to resolve this.
The idea is to disturb Kolusu's code as little as possible (cos it is easier that way).
For the fanatics, I'm afraid this includes an extra step.
The idea is to generate the SYMNAMES Kolusu later uses. A "Starting Point" date is used for SORTIN, so that production of the dates does not rely on the sort being run on a particular day.
Instead of the full date for the model holidays, only the MMDD is used as constants in the step. The year from the Starting Point is pre-pended. If the MMDD of the holiday is less than the MMDD of the Starting Point, Starting-Point-Plus-One is the year used.
The output from this step is to go to the SYMNAMES DD of Kolusu's first step, with the DD * data for that step removed.
I've slightly amended the output from the second step to included the amended day number to make testing easier. I have "lightly" tested it.
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
By any chance if you have SAS you could try something like,
Code:
year=2012;
Holiday_name = 'NEW YEAR';
MDY_sas = mdy(1,1,Year); /* JANUARY 1ST */
Wk_Day = weekday(MDY_sas);
if Wk_Day = 7 then MDY_sas = MDY_sas - 1;/*-1 IF IT IS SATURDAY*/
else if wk_day = 1 then MDY_sas = MDY_sas + 1; /*+1 IF IT IS SUNDAY*/
output;
Holiday_name = 'MEMORIAL DAY';
MDY_sas = intnx('week.2',mdy(5,31,year),0); /* LAST MONDAY MAY */
output;
or the HOLIDAY function in SAS 9.2(HOLIDAY function does not take into account if the holiday falls on saturday or sunday)
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
We are now back to two steps. For the solution, anyway. There is a new first step, for testing only. It generates 364 days starting from, and including, the date supplied on SYSIN. NB the date chosen should be the same as that used as the Starting Point for the holiday calendar. The SORTOUT is later input into the second step of the solution (the third here, everyone keeping up?).
That step has further minor amendments. The ANY/NONE combination has been replaced with OUTREC and a single WHEN=INIT, and there is some "self-testing" included. If a weekend is identified as the output date, the date is saved to the right and obliterated with XXXXXXXX. This has been tested by changing the 1,7 to 1,6.
The OUTFIL lists, for testing purposes only, all dates which had to be changed to get a working day. Remove the OUTFIL for generating required dates.
I just realized that there is a bug in second step where I am validating input file with the holidays. Can anyone pick that bug? 
