BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
omanlapaz
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

6 REPLIES 6
stat_sas
Ammonite | Level 13

%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
Amethyst | Level 16

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
omanlapaz
Calcite | Level 5

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

jakarman
Barite | Level 11

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 --<-----
Loko
Barite | Level 11

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
Super User

Check two functions:  WEEK()  and HOLIDAY()

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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