topic Re: SAS loop (until ? while ?) in SAS Programming

SAS loop (until ? while ?)

othmane1984 — Thu, 20 Oct 2016 14:31:41 GMT

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

Re: SAS loop (until ? while ?)

Cynthia_sas — Thu, 20 Oct 2016 16:51:15 GMT

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

Re: SAS loop (until ? while ?)

othmane1984 — Thu, 20 Oct 2016 17:11:20 GMT

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

Re: SAS loop (until ? while ?)

Cynthia_sas — Thu, 20 Oct 2016 17:34:56 GMT

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

Re: SAS loop (until ? while ?)

othmane1984 — Thu, 20 Oct 2016 19:12:50 GMT

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.

Re: SAS loop (until ? while ?)

Cynthia_sas — Thu, 20 Oct 2016 22:51:13 GMT

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

Re: SAS loop (until ? while ?)

ChrisNZ — Thu, 20 Oct 2016 23:16:38 GMT

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

Re: SAS loop (until ? while ?)

othmane1984 — Fri, 21 Oct 2016 07:28:24 GMT

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;