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
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
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
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 :So 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.
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 |
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.