topic Re: Sas data set inner loop in SAS Programming

Sas data set inner loop

Bipasha — Wed, 12 Apr 2017 12:17:09 GMT

Hi. I am new at sas. I need help in solving the following problem.
This problem is in context of loan taken from bank with given guarantee.
Here a loan can have multiple guarantees and a guarantee can be link to multiple loans.

My objective is to allocate the guarantees to respective loans based on the rank given.
So we have loan rank and guarantee rank.
We start with the loan with lowest rank first then get all the guarantees allocated to it and start allocating guarantee to the loan based on the guarantee rank.
For example take the following dataset

Loan_id guarantee_id loan_rank guarantee_rank loan_amount gurantee_amount
A1 g1 1 1 1000 100
A1 g2 1 2 1000 50
A2 g1 2 1 500 100
A3 g3 3 1 400 200

Now starting from rank 1 loan we allocate g1 to it and upadte value of g1 in row 3 as 0.

So we have:

Loan_id guarantee_id loan_rank guarantee_rank loan_amount guarantee_amount allocated unallocated remaining_guarantee
A1 g1 1 1 1000 100 100 900 0
A1 g2 1 2 1000 50
A2 g1 2 1 500 0
A3 g3 3 1 400 200

Then g2 get allocated to a1 and results in:

A1 g1 1 1 1000 100 100 900 0
A1 g2 1 2 1000 50 50 850 0
A2 g1 2 1 500 0
A3 g3 3 1 400 200

Notice that unallocated=previous unallocated-guarantee amount

Similarly proceding we have final output as

A1 g1 1 1 1000 100 100 900 0
A1 g2 1 2 1000 50 50 850 0
A2 g1 2 1 500 0 0 500 0
A3 g3 3 1 400 200 200 200 0

I have written the following code :

Data temp;
  Input loan_id $ guarantee_id $ loan_rank guarantee_rank loan_amount guarantee_amount;
datalines;
A1 g1 1 1 1000 100
A1 g2 1 2 1000 50
A2 g1 2 1 500 100
A3 g3 3 1 400 200
;
Run;

Proc sort data=temp;by loan_id guarantee_id;

Data temp1;
  Set temp;
  Retain unallocated;
  If guarantee_rank eq 1 and guarantee_amount>=loan_amount then do;
    Allocated=loan_amount;
    Unallocated=0;
    Remaining_guarantee=guarantee_amount-loan_amount;
  End;
  If guarantee_rank eq 1 and guarantee_amount<loan_amount then do;
    Allocated=guarantee_amount;
    Unallocated=loan_amount-guarantee_amount;
    Remaining_guarantee=0;
  End;
  If guarantee_rank ne 1 and guarantee_amount>=unallocated then do;
    Allocated=unallocated;
    Unallocated=0;
    Remaining_guarantee=guarantee_amount-unallocated;
  End;
  If guarantee_rank ne 1 and guarantee_amount<unallocated then do;
    Allocated=guarantee_amount;
    Unallocated=unallocated-guarantee_amount;
    Remaining_guarantee=0;
  End;
Run;

Here updation of guarantee_amount as the remaining_guarantee is not happening.

Can any body help me with this?

I really need this solved as soon as possible.

Thank you in advance.

Re: Sas data set inner loop

Astounding — Tue, 11 Apr 2017 16:53:29 GMT

Perhaps what you are asking about is the order of these three statements:

Allocated=unallocated;
Unallocated=0;
Remaining_guarantee=guarantee_amount-unallocated;

Maybe the second and third statements should be switched.

Re: Sas data set inner loop

Bipasha — Wed, 12 Apr 2017 03:20:19 GMT

Hi. Thank you for your input. But my problem is I wanl to update the
guarantee_amount to the remaining_guarantee for the total remaining rows
where it guarantee_id is g1. This has to be done for every guarantee.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Sas data set inner loop

lakshmi_74 — Wed, 12 Apr 2017 05:22:39 GMT

proc sort data=have;by loan_id;run;
data want;
set have;
by loan_id;
retain allocated unallocated;
allocated=guarantee_amount;
if first.loan_id then unallocated=loan_amount-allocated;
else unallocated=unallocated-allocated;
output;
if last.loan_id then do;
allocated=0;unallocated=0;
end;
run;

Re: Sas data set inner loop

Bipasha — Wed, 12 Apr 2017 10:37:19 GMT

Thanks for the efficient code.
The thing is this code does not update the guarantee_amount in the 3rd row
as g1 is already allocated to a1. Its value must be updated to 3rd row as 0.
This is the part I am strugging with.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Sas data set inner loop

Tom — Wed, 12 Apr 2017 11:26:50 GMT

Are you sure you don't need to add some ELSE statements in there? Did you mean for those IF/THEN statements to run in series like that? Currently if the first one is true and it modifies the variables then it could cause one of the later ones to be true also.

Re: Sas data set inner loop

Bipasha — Wed, 12 Apr 2017 12:15:07 GMT

I the above code I mensioned it gives output as

a1 g1 1 1 1000 100 100 900 0
a1 g2 1 2 1000 50 50 850 0
a2 g1 2 1 500 100 100 400 0
a3 g3 3 1 400 200 200 200 0

The problem I am facing is in the third row. What I want is, once a gurantee is allocated to a particular loan its value should be updated in te rest of the rows. Hence in third row, gurantee_amount should be 0, thus allocated=0 and unallocated=500.

So my desired output is:

a1 g1 1 1 1000 100 100 900 0
a1 g2 1 2 1000 50 50 850 0
a2 g1 2 1 500 0 0 500 0
a3 g3 3 1 400 200 200 200 0

You can give me totally new code if that works.

But for now yes I need these if statements to run in sequence and i dont need any else statement.

I didnt get your last concern. Please elaborate.