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

calling data in an excel file into a do loop

malakaext — Sun, 28 Oct 2012 00:17:14 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

PGStats — Sun, 28 Oct 2012 00:55:52 GMT

Simply read dataset test when creating dataset sim :

data test;
infile "F:\data\test.csv" firstobs=2 DSD MISSOVER;
input return resq;
run;

data sim;
retain a 0.3 sig 1.0;
set test;
v = rannor(123457);
z = v;
epsi = z*sig;
sig2 = resq + a*sig*sig;
sig = sqrt(sig2);
run;

You might also want to add (keep=...) option to dataset sim.

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

malakaext — Sun, 28 Oct 2012 01:08:56 GMT

I need to call entries in test one by one in the DO LOOP.

The code you have give above is missing the do loop and the out put is in complete.

Can you please fix it.

Thanks

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

Linlin — Sun, 28 Oct 2012 01:21:02 GMT

How many observations should have in your finale dataset? 1000*1000?

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

malakaext — Sun, 28 Oct 2012 01:26:31 GMT

I need a 1000 values for "epsi"

epsi = z*sig;

sig2 = resq + a*sig*sig;

What the program does is creating a 1000 epsi values from the do loop.

Since epsi = Z* sig and sig = SQRT(sig2) you need to use resq data one by one in sig2 = resq + a*sig*sig;

Thanks.

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

PGStats — Sun, 28 Oct 2012 01:33:18 GMT

If Linlins' guess is right, and you want to always start your iterations with the same values for a and sig, then you should use :

data sim;

a = 0.3;

sig = 1.0;

set test;

do i = 1 to 1000;

v = rannor(123457);

z = v;

epsi = z*sig;

sig2 = resq + a*sig*sig;

sig = sqrt(sig2);

output;

end;

run;

That will give you 1000 observations for each obs in dataset test.

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

malakaext — Sun, 28 Oct 2012 01:41:31 GMT

May be my explanation was not clear. So LET ME TRY AGAIN.

I need just a 1000 epsi values.

to calculate the 2nd epsi value you need the 2nd entry in the data set TEST.

to calculate the 3rd epsi value you need the 3rd entry in the data set TEST.

to calculate the 4th epsi value you need the 4th entry in the data set TEST.

and so on..

you dont have to use the 1st value of the data set TEST since you initially assign sig= 1

Thank you very much for all your help. I really appreciate it.

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

PGStats — Sun, 28 Oct 2012 01:49:16 GMT

Then I don't understand what was missing from my original solution. Did you try running it?

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

malakaext — Sun, 28 Oct 2012 01:59:30 GMT

I did.. but the output I got was incomplete.. I have attached it here so that you can also see it. Im using SAS 9.

Thanks.

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

Linlin — Sun, 28 Oct 2012 02:32:51 GMT

Hi,

Attached is the output dataset from PG's code.

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

malakaext — Sun, 28 Oct 2012 02:38:55 GMT

Can you please tell me how to write that to an excel sheet please.

I used the following code but it gave me incomplete results.. Is it because the version I use is 9.0?

data test;

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

input return resq;

run;

data sim;

retain a 0.3 sig 1.0;

set test;

v = rannor(123457);

z = v;

epsi = z*sig;

sig2 = resq + a*sig*sig;

sig = sqrt(sig2);

run;

LIBNAME dat "c:\sas\data\";

PROC EXPORT DATA=sim

OUTFILE="F:\data\e.csv"

DBMS=csv REPLACE;

RUN;

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

PGStats — Sun, 28 Oct 2012 02:45:19 GMT

It looks as if all resq values were missing from your test dataset : all variables that are calculated from the resq are missing in your output. In my tests (with SAS version 9.3, but we are not using any recent feature of the language) none of the columns contain missing values.

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

Tom — Sun, 28 Oct 2012 02:45:46 GMT

What does the number 1000 have to do with the problem?

If you have a dataset and you want to apply a formula to the values of the variables in it a normal data step will process all of the observations.

data sim;

set test ;

retain a 0.3 sig 1.0 ;

z = rannor(123457);

epsi = z*sig;

sig2 = resq + a*sig*sig;

sig = sqrt(sig2);

run;

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

malakaext — Sun, 28 Oct 2012 03:02:29 GMT

I have attached the output file. Can you please tell me why it it incomplete.

Im new to SAS. Can you also tell me if your code changes the sig value in each step. Because I use sig=1 only for the first calculation. starting from the second epsi value it should use SQRT( sig2) which is affected by each resq value in the data set TEST.

Thanks

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

malakaext — Sun, 28 Oct 2012 03:06:37 GMT

In the TEST file I have attached, resq values arn't missing!!! May be it's the version.

Thanks

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

Linlin — Sun, 28 Oct 2012 03:39:33 GMT

Hi,

the code below created the attached csv file. Please change the directory. The value of sig changes from Obs. to Obs.

data test;

infile "c:\temp\forum\test.csv" firstobs=2 DSD MISSOVER;

input return resq;

run;

data sim;

retain a 0.3 sig 1.0;

set test;

v = rannor(123457);

z = v;

epsi = z*sig;

sig2 = resq + a*sig*sig;

sig = sqrt(sig2);

run;

PROC EXPORT DATA=sim

OUTFILE="c\temp\forum\e.csv"

DBMS=csv REPLACE;

RUN;

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

malakaext — Sun, 28 Oct 2012 03:42:57 GMT

Thank you very much Linlin. your help is really appreciated.

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

art297 — Sun, 28 Oct 2012 03:50:41 GMT

I would suggest running everything, from the import to exporting the output, and post your log here. The log may show where the problem is.

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

malakaext — Sun, 28 Oct 2012 03:55:38 GMT

Here it is..

1869 data test;

1870 infile "F:\data\test.csv" firstobs=2 DSD MISSOVER;

1871 input return resq;

1872 run;

NOTE: The infile "F:\data\test.csv" is:

File Name=F:\data\test.csv,

RECFM=V,LRECL=256

NOTE: 1000 records were read from the infile "F:\data\test.csv".

The minimum record length was 9.

The maximum record length was 12.

NOTE: The data set WORK.TEST has 1000 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

1873

1874 data sim;

1875

1876 retain a 0.3 sig 1.0 ;

1877

1878 set test nobs=nobs ;

1879

1880 z = rannor(123457);

1881

1882 epsi = z*sig;

1883

1884 sig2 = resq + a*sig*sig;

1885

1886 sig = sqrt(sig2);

1887

1888 run;

NOTE: Missing values were generated as a result of performing an operation on

missing values.

Each place is given by: (Number of times) at (Line):(Column).

999 at 1882:11 1 at 1884:15 999 at 1884:19 1000 at 1886:9

NOTE: There were 1000 observations read from the data set WORK.TEST.

NOTE: The data set WORK.SIM has 1000 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.05 seconds

cpu time 0.03 seconds

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

art297 — Sun, 28 Oct 2012 04:20:51 GMT

The file you are initially reading is not the one you originally posted. When I read it my log shows:

The minimum record length was 20.

The maximum record length was 24.

Your log shows:

The minimum record length was 9.

The maximum record length was 12.

We both used the same code, thus I have to think we are not reading the same file.