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 loop (until ? while ?)

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-20-2016 10:31 AM

Dear,

I have a little issue coding a loop.

My Table looks like the following :

VAL1;VAL2

10;60

-100;60

50;100

-30;70

*****

What i want is :

- Calculating the sum of val1 ==> DONE .(-70)

- As this sum is < 0 i must do a loop to :

* Compensate the negative sum of VAL1 by the values of VAL2

In my example i have to compensate -70 , so from the first line the loop must take 60 and add it to val1 (remain -10 = -70 +60) ,

the loop goes to the second line and take 10 from val2 and add it to val1 ..==> END as we compensate the -70.

Hopping my explanation was clear .

Many thanks for your help

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

Posted in reply to othmane1984

10-20-2016 12:51 PM

Hi:

Can you show a table of your desired results? What you want for row 1 seems clear, but not for the other rows.

Thanks

cynthia

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

Posted in reply to othmane1984

10-20-2016 01:11 PM

Hi Cynthia

For thz first row it Will be

70; 10 as thé sum is -70 from thé first row we could take just 60 so we have to go to thé second Line which Will be

-90; 50 and we stop at thé second Line as we affected the -70.

If thé sum was bigger lets say -120 we have to loop to thé other lines...

Hope it was clear and many thanks in advance

For thz first row it Will be

70; 10 as thé sum is -70 from thé first row we could take just 60 so we have to go to thé second Line which Will be

-90; 50 and we stop at thé second Line as we affected the -70.

If thé sum was bigger lets say -120 we have to loop to thé other lines...

Hope it was clear and many thanks in advance

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

Posted in reply to othmane1984

10-20-2016 01:33 PM - edited 10-20-2016 01:34 PM

Hi, Sorry, I still don't get what you want. What will the ACTUAL adjusted numbers be on every row? From your original explanation, it sounded like you only wanted to adjust the value in VAL2, but now it sounds like you want to adjust the value in VAL1, as well. This sounds like you might need a DATA step program.

And, it's still not clear whether you want a new dataset as the output or whether you want a report. If you want a report, PROC REPORT might be able to do this, but what you want to see on the desired output report is still not clear. As an example, here is a report with VAL2 adjusted:

cynthia

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

Posted in reply to Cynthia_sas

10-20-2016 03:12 PM

Hi cynthia ,

i will try to explain myself better (and many thanks for your answers).

DATA HAVE;

VAL1 VAL2;

10;60

-100;60

50;100

-30;70

DATA WANT;

50;10

-90;50

50;100

-30;70

Q o how we got the two first rows ?

A: first we calculate the sum(val1) . the sum of this column must'nt be < 0 we have to get a sum(val1) equal 0

The algorithm :

if sum(val1) < 0 then ;

in our example the sum(val1) = -70;

those -70 must be taken from the val2 column (with a loop ..)

so we loop over the table :

first step in the loop :

- row _N_ = 1 : val1 = val1 + val2 ====> but as the first row we can only have 60 from val2 we must go to the second row (we still have 10 to add)

- row _N_ = 2 : val1 = val1 + 10

=======> the loop finish as we added 70 to the sum(val1)

If the first val2 was 70 the loop must stop.

if at the second row val2 was 5 we must go to the thirs row

and so on till we have the sum(val1) = 0.

Hope this time i was clear enough.

and many thanks cynthia.

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

Posted in reply to othmane1984

10-20-2016 06:51 PM

Hi,

Perhaps someone else understands what you want to do. You indicate that "-70 must be taken from the VAL2 column", but then you adjust both VAL1 and VAL2.

cynthia

Perhaps someone else understands what you want to do. You indicate that "-70 must be taken from the VAL2 column", but then you adjust both VAL1 and VAL2.

cynthia

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

Posted in reply to Cynthia_sas

10-20-2016 07:16 PM

Like this?

```
data HAVE;
input VAL1 VAL2;
cards;
10 60
-100 6
50 100
-30 70
run;
data WANT;
if _N_=1 then do until(LASTOBS);
set HAVE end=LASTOBS;
VAL3+VAL1;
end;
retain VAL3;
set HAVE;
if VAL3<0 then do;
VAL3+VAL2;
if VAL3>0 then VAL3=0;
end;
run;
```

VAL1 | VAL2 | VAL3 |
---|---|---|

10 | 60 | -10 |

-100 | 6 | -4 |

50 | 100 | 0 |

-30 | 70 | 0 |

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

Posted in reply to Cynthia_sas

10-21-2016 03:27 AM - edited 10-21-2016 03:28 AM

Hi ,

i will take a real example , hopping this time that it will be clear (and sorry again for bothering).

--------------------------------------------------------------------------------------------------------------------------

```
data HAVE;
input day BANK1 BANK2;
cards;
1 10 60
2 -100 60
3 50 100
4 -30 70
run;
Proc sql noprint ;
select sum(val1) into: sum
from have ;
run ;
/* As the a bank-account can't be < 0 , we must take money from bank2 and put it in bank1*/
/* The total money to be taken is equal to -70*/
```

So , the SUM (=-70)

We must take this money from bank2 and put it in bank1 ==> So more money for BANK1 and less money for BANK2 until we have the following dataset :

```
data WANT;
input day BANK1 BANK2 ValueRetrievedFromTheSUM;
cards;
1 70 0 60
2 -90 50 10
3 50 100 0
4 -30 70 0
run;
```