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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

4 REPLIES 4
gergely_batho
SAS Employee

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.

redfishJAX
Calcite | Level 5

Thank you!  I will give that a try.

Ksharp
Super User

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

redfishJAX
Calcite | Level 5

This worked great!  Thanks for you time.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1189 views
  • 4 likes
  • 3 in conversation