Help using Base SAS procedures

Do Loops with multiple sets using point=

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Do Loops with multiple sets using point=

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.


Accepted Solutions
Solution
‎04-28-2015 10:49 AM
Super User
Posts: 10,048

Re: Do Loops with multiple sets using point=

Posted in reply to redfishJAX

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

View solution in original post


All Replies
SAS Employee
Posts: 340

Re: Do Loops with multiple sets using point=

Posted in reply to redfishJAX

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.

Occasional Contributor
Posts: 18

Re: Do Loops with multiple sets using point=

Posted in reply to gergely_batho

Thank you!  I will give that a try.

Solution
‎04-28-2015 10:49 AM
Super User
Posts: 10,048

Re: Do Loops with multiple sets using point=

Posted in reply to redfishJAX

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

Occasional Contributor
Posts: 18

Re: Do Loops with multiple sets using point=

This worked great!  Thanks for you time.

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 289 views
  • 4 likes
  • 3 in conversation