topic calling data in an excel file into a do loop in SAS Forecasting and Econometrics

calling data in an excel file into a do loop

malakaext — Sun, 28 Oct 2012 00:16:27 GMT

The data set "test" contains two cloums return and RESQ each column has a 1000 values.

I need to access these 1000 values of RESQ in the do loop given in the program(sig2 = resq + b*sig*sig;).

Can someone please help me with this. (data set test is attached)

Thanks.

data test;

infile "F:\data\test.csv" DSD MISSOVER;

input return resq;

run;

data sim;

a=0.3;

sig = 1.0;

do j = 1 to 1000;

v = rannor(123457);

z = v;

epsi = z*sig;

sig2 = resq + a*sig*sig;

sig = sqrt(sig2);

output ;

end;

Re: calling data in an excel file into a do loop

ghastly_kitten — Mon, 29 Oct 2012 12:24:22 GMT

Would you be so kind as to tell a little more about the problem you're solving. In general.

Is it right that you need to perform the 'sig2' calculation for each row in test?

Then you just do this:

data test;

infile "F:\data\test.csv" DSD MISSOVER;

input return resq;

run;

data sim;

set test;

retain a sig;

if _n_ = 1 then do;

a=0.3;

sig = 1.0;

end;

v = rannor(123457);

epsi = v*sig;

sig2 = resq + a*sig*sig;

sig = sqrt(sig2);

output ;

end;

Re: calling data in an excel file into a do loop

malakaext — Mon, 29 Oct 2012 16:12:35 GMT

Thank you for your reply.

my main concern is to calculate epsi values.

1st epsi = v* sig , where sig =1.

however, starting from next epsi value, the sigma you use changes according to sig2 value. and sig2 value uses the values in "test" . so the very first sig 2 value uses the previous sig value and the first entry of the data set "test" and so on..

Does your code do this?

Thanks

Malaka.

Re: calling data in an excel file into a do loop

Tom — Mon, 29 Oct 2012 16:59:06 GMT

It is using the proper value because of the RETAIN statement. But if you want to see the value of SIG used in the calculation of SIG2 in the output dataset then you can move the OUTPUT statement to BEFORE the re-assignment of new value of SIG.

Re: calling data in an excel file into a do loop

ghastly_kitten — Mon, 29 Oct 2012 16:59:34 GMT

I'm quite sure it does.

Let's go through the code again with comments.

But before... do you understand the mechanics of data step?

Everything is quite easy.

SAS just goes through all commands between data and run statement and perform commands.

If there is no explicit output statement, SAS performs output just after the last command and before run.

The set statement just reads a row (next row) from data set (test in our case).

/* this is the part, where you read test sample */

data test;

infile "F:\data\test.csv" DSD MISSOVER;

input return resq;

run;

/* we are going to perform data step and write results to sim */

data sim;

/* take a new row from test */

set test;

/* here we tell that 'a' and 'sig' will not be reseted while passing set statement. That means if I set 'a' to 0.3 on the first row and will not change it through the code, it'll be 0.3 for each row */

retain a sig;

/* init values on the first row. _n_ - is automic variable which is the 'number of row' or amount of times you performed set */

if _n_ = 1 then do;

a=0.3;

sig = 1.0;

end;

/* for each line we make a new random variable 'v' */

v = rannor(123457);

/* epsi is set to the value v * sig. On the first time we go through this line 'sig' is equal to 1.0. However, since it retained and is modified later, */

/* we will have here the value from previous line (calculated in (X) row) */

epsi = v*sig; /* the (Y) row */

sig2 = resq + a*sig*sig;

/* Here is the row (X). sig is now has the value of sqrt of sig2 and this value will be used for the next row in 'test' in row (Y) */

sig = sqrt(sig2);

/* write results to sim dataset */

output ;

run;