topic Re: Issue with the loop in SAS Programming

Issue with the loop

Di_Pat — Fri, 07 Oct 2016 17:03:31 GMT

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.

Re: Issue with the loop

Astounding — Fri, 07 Oct 2016 18:15:14 GMT

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.

Re: Issue with the loop

Di_Pat — Fri, 07 Oct 2016 18:35:25 GMT

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.

Re: Issue with the loop

ballardw — Fri, 07 Oct 2016 20:18:55 GMT

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

Re: Issue with the loop

Di_Pat — Sun, 09 Oct 2016 20:05:04 GMT

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

Re: Issue with the loop

Di_Pat — Mon, 10 Oct 2016 17:43:38 GMT

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