topic Re: Create A Schedule in SAS Programming

Create A Schedule

omanlapaz — Tue, 26 Aug 2014 01:12:38 GMT

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.

Re: Create A Schedule

stat_sas — Tue, 26 Aug 2014 03:27:05 GMT

%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;

Re: Create A Schedule

Jagadishkatam — Tue, 26 Aug 2014 03:30:13 GMT

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

Re: Create A Schedule

jakarman — Tue, 26 Aug 2014 06:23:37 GMT

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

Re: Create A Schedule

Loko — Tue, 26 Aug 2014 09:57:43 GMT

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;

Re: Create A Schedule

Ksharp — Tue, 26 Aug 2014 12:31:21 GMT

Check two functions: WEEK() and HOLIDAY()

Re: Create A Schedule

omanlapaz — Tue, 26 Aug 2014 16:11:14 GMT

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