10-07-2016 01:03 PM
I am running below steps and output is not generated.
Step 1) Converting Holidays into macro variable for last four years - so total 40 macro variables
Step 2) Issue lies here - in do loop, check_4 is generating missing observations for all records.
/* For holiday, I am running below link with minor twerks
proc sql noprint;
%put &day_off40 ;
/* check_3 is mmddyy8. format, trying to add additional day if holiday falls on check_3 or within last three days of it */
data trial4 ;
set trial ;
do i = &day_off1. to &day_off40. ;
if check_3 - 1 = i then check_4 = check_3+1 ;
else if check_3 - 2 = i then check_4 = check_3+1 ;
else if check_3 - 3 = i then check_4 = check_3+1 ;
else if check_3 - 0 = i then check_4 = check_3+1 ;
else check_4 = check_3 ;
format check_4 mmddyy8. ;
Please suggest some good do loop documents as well.
10-07-2016 02:15 PM
This is a wild guess since you really haven't shown us any of the data values. You can confirm my guess in part by showing the results of the %PUT statement.
The IF/THEN logic for creating CHECK4 always assigns it a value. The only way it could be missing is if the DO loop never executes. That could happen if you have dates in MM/DD/YY format for your 40 dates. For example, the DO loop might actually look like this:
do i=02/14/10 to 12/25/15;
That could conceivably be the results of substituting for your macro variables. That DO loop won't execute at all, because the starting value is greater than the ending value. All the "/" characters merely represent division, not a date. So "2 divided by 14 divided by 10" is greater than "12 divided by 25 divided by 15" and the program skips executing the DO loop.
The right approach would be to load the 40 values into an array (macro language is optional, but not required). Use a DO loop that goes from 1 to 40, each time referring to one of the elements in the array.
10-07-2016 02:35 PM
You are absolutely right. format was creating the issue. Thank you for sparing time and explaining in detail. I bet I would have never found it in any documentation.
New problem is -- else check_4 = check3 ; overwrites everything
I will have to write additional step to replace the missing dates.
10-07-2016 04:18 PM
There really isn't a need to use the macro variable here:
If you do
into ay_off1-ay_off9999 SAS will only create those needed.
If you later need the number involve you can get Nobs using SQLOBS macro varaible;
proc sql noprint; select Day_off into :Day_off1-:Day_off9999 from holidays2; quit; %let Nobs = &sqlobs;
And the loop might look like
do i = &day_off1. to &&day_off&nobs;
If I understand what your are attempting the multiple IF may be replace with
If i - check_3 le 3 then check_4=check_3+1;
maybe the subtraction should be check_3 - i though. Hard to be sure without values and what the result should be.
Note that if those variabls are indeed dates then the macro variables will look more like 207123
10-10-2016 01:43 PM
Your solution is not working either. My observations are increasing. Below is the data I am looking forward too
Also if holiday is on weekend, then no need to add additional day.
data have ;
List of few holidays
data Want ;
App Duedate Updated
001 07/03/15 07/03/15
002 07/04/15 07/05/15
003 08/31/16 08/31/16
004 11/09/16 11/09/16
005 11/25/16 11/26/16
006 11/30/16 12/01/16
007 12/27/16 12/28/16