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()
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.