DATA Step, Macro, Functions and more

Issue with the loop

Occasional Contributor
Posts: 13

Issue with the loop

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;
  select count(*)
  into :NObs
  from holidays2;
  select Day_off
  into Smiley Very Happyay_off1-Smiley Very Happyay_off%left(&NObs)
  from holidays2;
%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. 

Thank you.

Super User
Posts: 6,628

Re: Issue with the loop

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.

Occasional Contributor
Posts: 13

Re: Issue with the loop

Posted in reply to Astounding

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.

Super User
Posts: 13,299

Re: Issue with the loop

There really isn't a need to use the macro variable here:

 into Smiley Very Happyay_off1-Smiley Very Happyay_off%left(&NObs)

If you do

 into Smiley Very Happyay_off1-Smiley Very Happyay_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;
%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

Occasional Contributor
Posts: 13

Re: Issue with the loop

Thanks for reply. Will try tomorrow and keep you posted. If it wont work then I will post some data to have a peek. 


Occasional Contributor
Posts: 13

Re: Issue with the loop

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



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

Ask a Question
Discussion stats
  • 5 replies
  • 1 like
  • 3 in conversation