DATA Step, Macro, Functions and more

Create A Schedule

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Create A Schedule

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 IDStart DateEnd DatePayment Payment Frequency
18/25/20149/29/201420Daily

I would like my output data to look this:

Item ID Payment DatePayment
18/25/201420
18/26/201420
1"20
1"20
19/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.


Accepted Solutions
Solution
‎08-25-2014 11:30 PM
Trusted Advisor
Posts: 1,137

Re: Create A Schedule

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

Thanks,

Jag

Thanks,
Jag

View solution in original post


All Replies
Trusted Advisor
Posts: 1,231

Re: Create A Schedule

Posted in reply to omanlapaz

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

Solution
‎08-25-2014 11:30 PM
Trusted Advisor
Posts: 1,137

Re: Create A Schedule

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

Thanks,

Jag

Thanks,
Jag
New Contributor
Posts: 2

Re: Create A Schedule

Posted in reply to Jagadishkatam

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

Trusted Advisor
Posts: 3,215

Re: Create A Schedule

Posted in reply to omanlapaz

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

---->-- ja karman --<-----
Super Contributor
Posts: 308

Re: Create A Schedule

Posted in reply to omanlapaz

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;

Super User
Posts: 10,048

Re: Create A Schedule

Posted in reply to omanlapaz

Check two functions:  WEEK()  and HOLIDAY()

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 332 views
  • 6 likes
  • 6 in conversation