topic Re: Do Until Loop Help in SAS Procedures

Do Until Loop Help

redfishJAX — Thu, 21 Jan 2010 15:30:46 GMT

I have two datasets: Paid Items and Payments
Here are the samples:

Payments:
key py_amt
1 25.00
2 25.00

Paid Items:
key oblgid pi_amt
1 123 25.00
2 124 25.00

Here is what I have...it partially works, but what I need it to do is mark the observation in Paid Items as found as well. I need the oblgid in the output and it keeps looping back to the first ob in Paid Items when it goes to the next observation in the payments.

Here is the code:

data py_key;
set payments;
by key;
retain first1;
if first.key then first1=_n_;
if last.key then
do;
last1=_n_;
output;
end;
run;
data pi_key;
set paid_items;
by key;
retain first2;
if first.key then first2=_n_;
if last.key then
do;
last2=_n_;
output;
end;
run;

data keys (keep=key first1 last1 first2 last2);
merge py_key(in=a) pi_key (in=b);
by key;
if a;
run;

data final;
set keys;
do i=first1 to last1;
set payments point=i;
if first2 ne . then
do j=first2 to last2 until(found);
set paid_items point=j;
if py_amt = pi_amt then do;

found=1;
end;
else if py_amt <= pi_amt then do;
found=1;
end;
end;
if first2 = '.' then delete;

output;

end;
run;

Can't seem to figure it out...

Re: Do Until Loop Help

DanielSantos — Thu, 21 Jan 2010 16:40:50 GMT

Your message seem to be incomplete.

Please give us a sample of the resulting dataset.

Cheers from Portugal

Daniel Santos @ www.cgd.pt

Re: Do Until Loop Help

abdullala — Thu, 21 Jan 2010 17:57:07 GMT

found it difficult to understand your need. can you simply give several lines of original data, and also several lines of the wanted results?

Re: Do Until Loop Help

redfishJAX — Thu, 21 Jan 2010 17:58:20 GMT

Sorry about that...for some reason it won't allow me to post the entire text...

data payments;
input key_id $ amt;
datalines;
abc 50
abc 50
;
data paid_items;
input key_id $ oblgid $ amt;
datalines;
abc 50 123
abc 50 124

This is the result I am looking for:

data result;
abc 123 50
abc 124 50

This is the code I have so far, but it loops back through the observations for paid_items for every
observation not found in the payments. I want to mark the paid_item observation as found also so it does not find
that match again.

data py_key;
set payments;
by key_id;
retain first1;
if first.key_id then first1=_n_;
if last.key_id then
do;
last1=_n_;
output;
end;
run;

data pi_key;
set paid_items;
by key_id;
retain first2;
if first.key_id then first2=_n_;
if last.key_id then
do;
last2=_n_;
output;
end;
run;

data keys (keep=key_id first1 last1 first2 last2);
merge py_key(in=a) pi_key (in=b);
by key_id;
if a;
run;

data final;
set keys;
do i=first2 to last2;
set paid_items point=i;
if first1 ne . then
do j=first1 to last1 until(found);
set payments point=j;
if py_amt le pi_amt then do;
found=1;
end;
end;
if first1 = '.' then delete;
output;
end;
run;

Re: Do Until Loop Help

DanielSantos — Fri, 22 Jan 2010 08:31:19 GMT

Hi.

I'm sorry, but still I'm not quite sure about what you are trying to do.

From your example and looking at your code, it seems to me you are trying to do a N-to-N match by KEY_ID using, is this correct?

If so, N-to-N match is not feasible through MERGE (at least, in a easy way), but you can achieve that with a simple SQL inner join, like this:
[pre]
proc sql noprint;
create table RESULT as
select a.KEY_ID, a.AMT, b.OBLGID
from PAYMENTS as a inner join PAID_ITEMS as b on a.KEY_ID = b.KEY_ID;
quit;
[/pre]
Cheers from Portugal

Daniel Santos @ www.cgd.pt