Data visualization with SAS programming

calling data in an excel file into a do loop

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 91
Accepted Solution

calling data in an excel file into a do loop

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*sigSmiley Wink.

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;

Attachment

Accepted Solutions
Solution
‎10-27-2012 08:55 PM
Respected Advisor
Posts: 4,609

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

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.

PG

PG

View solution in original post


All Replies
Solution
‎10-27-2012 08:55 PM
Respected Advisor
Posts: 4,609

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

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.

PG

PG
Frequent Contributor
Posts: 91

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

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

Super Contributor
Posts: 1,636

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

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

Frequent Contributor
Posts: 91

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

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.

Respected Advisor
Posts: 4,609

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

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.

PG

PG
Frequent Contributor
Posts: 91

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

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.

Respected Advisor
Posts: 4,609

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

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

PG
Frequent Contributor
Posts: 91

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

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.output.jpg

Thanks.

Super Contributor
Posts: 1,636

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

Hi,

Attached is the output dataset from PG's code.

Attachment
Frequent Contributor
Posts: 91

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

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;

Respected Advisor
Posts: 4,609

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

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.

PG

PG
Frequent Contributor
Posts: 91

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

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

Thanks

Super Contributor
Posts: 1,636

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

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;

Attachment
Frequent Contributor
Posts: 91

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 22 replies
  • 568 views
  • 14 likes
  • 5 in conversation