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.
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
%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;
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
thank you so much.. this works perfectly!!!!
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
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;
Check two functions: WEEK() and HOLIDAY()
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.