turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Sas data set inner loop

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 11:43 AM - edited 04-12-2017 08:17 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 12:53 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 11:20 PM

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. -##

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. -##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-12-2017 01:22 AM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-12-2017 06:37 AM

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. -##

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. -##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-12-2017 07:26 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-12-2017 08:15 AM

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.