topic Re: how to write a loop for this data structure in SAS Programming

how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 14:19:39 GMT

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!

Re: how to write a loop for this data structure

ed_sas_member — Mon, 30 Dec 2019 14:24:01 GMT

Hi @superbug

Could you please provide some sample data to play with?

Re: how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 14:28:55 GMT

@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!!

Re: how to write a loop for this data structure

PaigeMiller — Mon, 30 Dec 2019 14:36:23 GMT

@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.

Re: how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 14:48:46 GMT

@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!

Re: how to write a loop for this data structure

Kurt_Bremser — Mon, 30 Dec 2019 15:15:56 GMT

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.

Re: how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 15:49:06 GMT

@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

thanks!

Re: how to write a loop for this data structure

Kurt_Bremser — Mon, 30 Dec 2019 16:14:56 GMT

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:

Re: how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 17:20:27 GMT

@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

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!!

Re: how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 17:30:01 GMT

@Kurt_Bremser

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

a b

15 1

Re: how to write a loop for this data structure

Kurt_Bremser — Mon, 30 Dec 2019 17:30:40 GMT

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

Re: how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 17:36:37 GMT

@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!

Re: how to write a loop for this data structure

novinosrin — Mon, 30 Dec 2019 18:10:05 GMT

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!

Re: how to write a loop for this data structure

superbug — Mon, 30 Dec 2019 18:26:32 GMT

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

And thanks to you all for the help.