BookmarkSubscribeRSS Feed
Di_Pat
Fluorite | Level 6

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.

5 REPLIES 5
Astounding
PROC Star

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.

Di_Pat
Fluorite | Level 6

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.

ballardw
Super User

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

Di_Pat
Fluorite | Level 6

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

 

Di_Pat
Fluorite | Level 6

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 966 views
  • 1 like
  • 3 in conversation