DATA Step, Macro, Functions and more

SAS loop (until ? while ?)

Reply
New Contributor
Posts: 4

SAS loop (until ? while ?)

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

 

 

SAS Super FREQ
Posts: 8,864

Re: SAS loop (until ? while ?)

Posted in reply to othmane1984

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

show_desired_result.png

New Contributor
Posts: 4

Re: SAS loop (until ? while ?)

Posted in reply to othmane1984
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
SAS Super FREQ
Posts: 8,864

Re: SAS loop (until ? while ?)

[ Edited ]
Posted in reply to othmane1984

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 possible_desired_output.pngVAL2 adjusted:



cynthia

New Contributor
Posts: 4

Re: SAS loop (until ? while ?)

Posted in reply to Cynthia_sas

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

 

Smiley Frustratedo 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.

SAS Super FREQ
Posts: 8,864

Re: SAS loop (until ? while ?)

Posted in reply to othmane1984
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
PROC Star
Posts: 1,759

Re: SAS loop (until ? while ?)

Posted in reply to Cynthia_sas

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

 

New Contributor
Posts: 4

Re: SAS loop (until ? while ?)

[ Edited ]
Posted in reply to Cynthia_sas

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;

 

 

Ask a Question
Discussion stats
  • 7 replies
  • 372 views
  • 0 likes
  • 3 in conversation