turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Analytics
- /
- Forecasting
- /
- calling data in an excel file into a do loop

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-27-2012 08:16 PM

Hi

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-29-2012 08:24 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-29-2012 12:12 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-29-2012 12:59 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-29-2012 12:59 PM

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;