BookmarkSubscribeRSS Feed
othmane1984
Calcite | Level 5

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

 

 

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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

othmane1984
Calcite | Level 5
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
Cynthia_sas
SAS Super FREQ

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

othmane1984
Calcite | Level 5

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.

Cynthia_sas
SAS Super FREQ
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
ChrisNZ
Tourmaline | Level 20

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

 

othmane1984
Calcite | Level 5

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1120 views
  • 0 likes
  • 3 in conversation