topic Do Loops with multiple sets using point= in SAS Procedures

Do Loops with multiple sets using point=

redfishJAX — Thu, 23 Apr 2015 14:33:07 GMT

I am stumped on how to get something to work.

I need to use point= do loop to accompish the following:

Dataset1: Promise

acctnum promisedate promiseamt bwindow ewindow
100 3/15/2015 100 3/15/2015 3/28/2015
100 3/21/2015 150 3/21/2015 4/1/2015

Dataset2: Payments
acctnum paydate payamt
100 3/21/2015 100
100 3/25/2015 150

I need the to evaluate each promise record by acctnum for a matching payment record (with same acctnum) within the date range (pdate and ewindow) and mark the promise found,
but I also need to mark the payment as used so it is not used again to evaluate the next promise record for that account number.
What i have is below, but the issue is the first payment is used twice becuase it is the first payment in the specified range for both promises. Anyone have any ideas?

data promisekey;
set promise;
by acctnum ewindow;
retain firstpr;
if first.acctnum then firstpr=_n_;
if last.acctnum then
do;
lastpr=_n_;
output;
end;
run;

data paymentkey;
set payments;
by acctnum paydate;
retain firstpy;
if first.acctnum then firstpy=_n_;
if last.acctnum then
do;
lastpy=_n_;
output;
end;
run;

data keys (keep=acctnum firstpr lastpr firstpy lastpy);
merge promisekey (in=a) paymentkey;
by acctnum;
if a;
run;

data final;
set keys;

do i=firstpr to lastpr;
set promise point=i;
format recdate yymmddn8.;
format bwindow yymmddn8.;
format ewindow yymmddn8.;
format paydate yymmddn8.;
format paid comma21.2;
recdate=.;
paid=.;

if firstpy ne . then
do j=firstpy to lastpy until (found);
found=0;
   set payments point=j ;
if paydate ge pdate and paydate le ewindow then do;
   recdate=paydate;
   paid=amount;
   found=1;

end;
end;
output;
end;
run;

This is what outputs (trimmed down with only some variables):

acctnum promisedate promiseamt ewindow found paydate payamt

100 3/15/2015 100 3/28/15 1 3/21/15 100
100 3/21/2015 150 4/1/2015 1 3/21/15 100

This is what i need it to output:

acctnum promisedate promiseamt ewindow found paydate payamt

100 3/15/2015 100 3/28/15 1 3/21/15 100
100 3/21/2015 150 4/1/2015 1 3/25/15 150

Sorry for the long winded request.. but wasn't sure how to explain and still not sure if I made it clear enough.

Re: Do Loops with multiple sets using point=

gergely_batho — Tue, 28 Apr 2015 10:25:41 GMT

I think one solution could be to read Payments into a hash object.

Than read Promise sequentially (set statement). For each promise you search the hash object (for example by iterating over it). If a match is found, you remove that entry from the hash object. So a subsequent search will not find it.

Re: Do Loops with multiple sets using point=

redfishJAX — Tue, 28 Apr 2015 11:26:50 GMT

Thank you! I will give that a try.

Re: Do Loops with multiple sets using point=

Ksharp — Tue, 28 Apr 2015 14:49:12 GMT

I have already done it before for somebody .

data Promise ;
input acctnum  promisedate : mmddyy12. promiseamt (bwindow  ewindow ) (: mmddyy12.);
format promisedate bwindow ewindow mmddyy10.;
cards;
100  3/15/2015 100  3/15/2015 3/28/2015
100  3/21/2015 150  3/21/2015 4/1/2015
200  3/15/2015 100  3/15/2015 3/28/2015
200  3/21/2015 150  3/21/2015 4/1/2015
;
run;

 

data Payments ;
input acctnum  paydate  : mmddyy12. payamt ;
format       paydate   mmddyy10.;
cards;
100  3/21/2015 100
100  3/25/2015 150
100  5/25/2015 150
200  3/21/2015 100
200  3/25/2015 150
200  3/28/2015 150
200  5/25/2015 150
;
run;

data want;
 if _n_ eq 1 then do;
  if 0 then set Promise(keep=acctnum  bwindow ewindow); 
  declare hash h(dataset:'Promise(keep=acctnum  bwindow ewindow)',multidata:'y');
  h.definekey('acctnum');
  h.definedata('bwindow','ewindow');
  h.definedone();
 end;
set  Payments;
rc=h.find();
do while(rc=0);
 if bwindow le paydate le ewindow then do;
                                          found=1;
                                                    h.removedup();
                                          leave;
                                       end;
 rc=h.find_next();
end;
drop rc;
run;

Xia Keshan

Re: Do Loops with multiple sets using point=

redfishJAX — Wed, 29 Apr 2015 17:18:40 GMT

This worked great! Thanks for you time.