Create A Schedule

[omanlapaz]

Hi All,

I appreciate any help that you can give. Hopefully, this isn't too difficult to do.

I am looking to create a payment schedule for purchased items.

My original data looks like this:

Item ID	Start Date	End Date	Payment	Payment Frequency
1	8/25/2014	9/29/2014	20	Daily

I would like my output data to look this:

Item ID	Payment Date	Payment
1	8/25/2014	20
1	8/26/2014	20
1	"	20
1	"	20
1	9/29/2014'	20

The tricky part is that I do not want to include weekends or holidays of my choosing. Also, if the payment  frequency is weekly or monthly--I would like to have the payment schedule reflect that. I appreciate any help that you give. Thanks.

[stat_sas]

%LET HOLIDAY='27AUG2014'D '30AUG014'D '09SEP2014'D '16SEP2014'D;

data want (keep=item_id payment_date payment);
set have;
days = intck('weekday',start_date,end_date);
do i = 0 to days;
      payment_date = intnx('weekday',start_date,i);
      if payment_date NOT IN (&HOLIDAY.) then output;
end;
format payment_date date9.;
run;

proc print data=want;
run;

[Jagadishkatam · Accepted solution]

Alternatively,

data have;

  input

Item_ID    Start_Date :mmddyy9.    End_Date:mmddyy9.    Payment     Payment_Frequency$;

cards;

1    8/25/2014    9/29/2014    20    Daily

;

data want(where=(weekday not in ('  Saturday', '    Sunday')));

set have;

do expand_date=Start_Date to End_Date;

weekday=put(put(expand_date,EURDFDWN10.),10.);

output;

end;

format expand_date date9. ;

run;

in the where clause you can use the expand_date not in holiday date list as suggested by stat@sas.

Thanks,

Jag

[omanlapaz]

thank you so much.. this works perfectly!!!!

[jakarman]

If you are needing custom intervals  the use custom intervals SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition  Ther intervalds options can point to a dataset having your own definitions

[Loko]

Hello,

A starting point below:

data holiday;
input a date9.;
datalines;
26AUG2014
01SEP2014
;
run;

proc sql noprint;
select a into :holiday separated by " " from holiday;
quit;

%put &holiday;

data have;
input Item_ID Start_Date mmddyy10. End_Date mmddyy10. Payment Payment_Frequency  $;
datalines;
1 8/25/2014 9/29/2014 20 Daily
2 8/25/2014 9/29/2014 20 Monthly
;

proc format;
invalue $ freqs
  'Daily'='day'
  'Monthly'='month'
;
run;

data want(drop=i);
set have;
by Item_ID notsorted;
freq=input(Payment_Frequency,$freqs.);
i=0;

format payment_date date9.;
do while (payment_date lt End_Date);
payment_date=intnx(freq,Start_Date,i,'s');
if  payment_date gt End_Date then
  payment_date=End_Date;
if not(find("&holiday",compress(put(payment_date,best12.))))  then output;
i+1;
end;

run;

[Ksharp]

Check two functions:  WEEK()  and HOLIDAY()