how to write a loop for this data structure

[superbug]

Hello,

As in the attachment (part of my data), my data has two columns, the total number of column A is 224. Numbers in column is calculated as following:

B2=B1-A1/224

B3=B2-A2/224

B4=B3-A3/224

...

...

Could anyone help me how to write a loop in SAS to do this calculation.

thank you!

[ed_sas_member]

Hi @superbug 

Could you please provide some sample data to play with?

[superbug]

@ed_sas_member 

here is what my data look like, total number of column A is 224

             A         B

1          15       1

2           1         0.933

3           1         0.9286

4           1         0.9241

5           1          0.9196

6           2         0.9152

             .          .

             .          .

thank you!!

[Kurt_Bremser]

First of all, forget your Excel thinking. It will only hinder your SAS growth. SAS has observations (records or rows), and columns that have the same name in all observations. There are no "cells",

See this code:

data want;
set have;
b = ifn(_n_ = 1,b,lag(b) - lag(a) / 224);
run;

The ifn() function is necessary to keep b in the first observation, while still executing the lag() functions.

See if the code works for you, and try to understand it. Come back with any questions you have.

[superbug]

@Kurt_Bremser 

thank you!

using the code you provided, in the second column, I can only get the desired first two values, while the rest values in the second column are all missing. Here is what the result data look like. Please help me to revise the code to get all desired values in the second column.

15          1

 1            0.9330357

 1    

 1

 1

thanks!

[Kurt_Bremser]

Can't be.

Code (including source data):

data have;
input a b;
datalines;
15 1
1 0.933
1 0.9286
1 0.9241
1 0.9196
2 0.9152
;

data want;
set have;
b = ifn(_n_ = 1,b,lag(b) - lag(a) / 224);
run;

proc print data=want noobs;
run;

Result:

a	b
15	1.00000
1	0.93304
1	0.92854
1	0.92414
1	0.91964
2	0.91514

[superbug]

@Kurt_Bremser 

Thank you! If there are values in b column, the code did work.

My case is there's value for the first row of column b, I need to get all values of column b using information from column a. My data looks like the following

a      b    
15     1
1 
1 
1 
1 
2 ;

Total number of column a is 224. Using the code you suggested, I can only get the second number of b, which is 0.933. If I want to get the third number of column b, I have to use the information from "want" data, as following

data want1;
set want;
b = ifn(_n_ = 1,b,lag(b) - lag(a) / 224);
run;

If I want to get the fourth number of column b, I have to use the information from "want1" data. Since my data has thousands of rows, to do this kind of repetition is impossible. For my specific case, please help with quick efficient ways.

thank you!! 

[superbug]

@Kurt_Bremser 

oops, it seems the data structure changed after I post it. It look like this

a            b

15          1

1        

1

1

1

2

[Kurt_Bremser]

Please post example data in a working data step with datalines, as I did. That removes all ambiguities.

[superbug]

@Kurt_Bremser 

my data look like the following

data have;
input a    b;
datalines;
15         1
1
1
1
1
2
;

As you noticed, there is only value in the first row of variable b, I need to get all values of variable b. Using the following code, I can only get the second value of variable b. 

data want;
set have;
b=ifn(_n_=1, b, lag(b)-lag(a)/224);
run;

please help with how to get all values of variable b.

thank you so much!

[novinosrin · Accepted solution]

Hi @superbug  Your revised sample is a case for RETAIN statement. The following is a way to conveniently approach it. 

_IORC_ is an automatic variable that is freely available and retains without needing a specify in Retain statement.

data have;
infile cards truncover;
input a    b;
datalines;
15         1
1
1
1
1
2
;

data want;
 set have;
  b=ifn(_n_=1, b, _iorc_-lag(a)/224);
 _iorc_=b;
run;

Offer the credit to @Kurt_Bremser  and mark his solution as correct as the logic is still his idea with a tweak. Cheers!

[superbug]

@novinosrin   Awesome, it worked! Thank you!!!

And thanks to you all for the help.

[PaigeMiller]

---

@superbug wrote:

Hello,

As in the attachment (part of my data), my data has two columns, the total number of column A is 224. Numbers in column is calculated as following:

B2=B1-A1/224

B3=B2-A2/224

B4=B3-A3/224

...

...

Could anyone help me how to write a loop in SAS to do this calculation.

thank you!

---

Are these formulas like you would use in Excel??

The only way to make use of Excel style programming logic in SAS is to start over and re-formulate the problem in SAS programming logic, and probably re-format the data to work smoothly in SAS, as the two programming paradigms are generally are not compatible.

[superbug]

@PaigeMiller 

 I only have column A data. I need to write SAS code using the above formula to get values in column B.  

thank you!