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
http://www.sascommunity.org/wiki/Generating_Holiday_Lists
*/
proc sql noprint;
select count(*)
into :NObs
from holidays2;
select Day_off
into :Day_off1-:Day_off%left(&NObs)
from holidays2;
quit;
%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 ;
end;
format check_4 mmddyy8. ;
run;
Please suggest some good do loop documents as well.
Thank you.
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.
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.
There really isn't a need to use the macro variable here:
into :Day_off1-:Day_off%left(&NObs)
If you do
into :Day_off1-:Day_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;
else check_4=check_3;
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
Thanks for reply. Will try tomorrow and keep you posted. If it wont work then I will post some data to have a peek.
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 ;
App Duedate
001 07/03/16
002 07/04/16
003 08/31/16
004 11/09/16
005 11/25/16
006 11/30/16
007 12/27/16
List of few holidays
05/25/15
07/04/15
05/30/16
07/04/16
09/05/16
10/10/16
11/11/16
11/24/16
12/25/16
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
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.