Am running this code for OLS regression

`Q1=XB+error`

where `X={1,P1,P2,P3,P4,P5}`

my coding :

```
new;
cls;
fname = " data.xls";
//Range is column 'b' and column 'k'
range="b2:k28";
sheet=1;
//Load both columns into 'myvars' as a matrix
//using spreadSheetReadM
myvars=spreadSheetReadM(fname,range,sheet);
//Extract all rows of the first column
P1= myvars[.,1];
//Extract all rows of the second column
P2= myvars[.,2];
//Extract all rows of the third column
P3= myvars[.,3];
//Extract all rows of the fourth column
P4= myvars[.,4];
//Extract all rows of the fifth column
P5= myvars[.,5];
//Extract all rows of the sixth column
Q1= myvars[.,6];
//Extract all rows of the seventh column
Q2= myvars[.,7];
//Extract all rows of the eighth column
Q3= myvars[.,8];
//Extract all rows of the ninth column
Q4= myvars[.,9];
//Extract all rows of the tenth column
Q5= myvars[.,10];
call ols("", Q1,P1,P2,P3,P4,P5);
```

I got this error

#### Currently active call:

File xls.src, line 957, in `__handleXlsError`

`end;`

#### Traceback:

File xls.src, line 113, in `xlsReadM`

`retp(__handleXlsError("xlsReadM", file, err));`

File xls.src, line 738, in `spreadSheetReadM`

`retp(xlsreadm(file,...));`

## 1 Answer

0

I see a couple problems with the code snippet that you posted. The first is that you have an empty space in the file name string.

```
//Should not be a space in the string before the file name
fname = " data.xls";
//This is correct
fname = "data.xls";
```

Second, to use the GAUSS function `ols`

with matrices, the second input needs to be a vector which is the dependent variable and the third input needs to be a matrix, containing all columns of the dependent variable. This is shown in the code snippet below which should work for you.

```
new;
cls;
fname = "data.xls";
//Range is column 'b' and column 'g'
range="b2:g28";
sheet=1;
//Load both columns 2 to 7 into 'myvars' as a matrix
//using spreadSheetReadM
myvars = spreadSheetReadM(fname,range,sheet);
//Extract all rows of columns 1 to 5
P = myvars[.,1:5];
//Extract all rows of the sixth column
Q1 = myvars[.,6];
call ols("", Q1, P);
```

Finally, if you are using GAUSS 17 or GAUSS 18 and the first line of your Excel file contains variable names, you do not even have to load the data separately. If the header for the dependent variable (i.e. contents of cell G1) `Q1`

, and the headers for the `P`

columns are `P1`

, `P2`

, `P3`

, `P4`

, `P5`

, then you could just enter:

`call ols("data.xls", "Q1 ~ P1 + P2 + P3 + P4 + P5");`

## Your Answer

## 1 Answer

I see a couple problems with the code snippet that you posted. The first is that you have an empty space in the file name string.

```
//Should not be a space in the string before the file name
fname = " data.xls";
//This is correct
fname = "data.xls";
```

Second, to use the GAUSS function `ols`

with matrices, the second input needs to be a vector which is the dependent variable and the third input needs to be a matrix, containing all columns of the dependent variable. This is shown in the code snippet below which should work for you.

```
new;
cls;
fname = "data.xls";
//Range is column 'b' and column 'g'
range="b2:g28";
sheet=1;
//Load both columns 2 to 7 into 'myvars' as a matrix
//using spreadSheetReadM
myvars = spreadSheetReadM(fname,range,sheet);
//Extract all rows of columns 1 to 5
P = myvars[.,1:5];
//Extract all rows of the sixth column
Q1 = myvars[.,6];
call ols("", Q1, P);
```

Finally, if you are using GAUSS 17 or GAUSS 18 and the first line of your Excel file contains variable names, you do not even have to load the data separately. If the header for the dependent variable (i.e. contents of cell G1) `Q1`

, and the headers for the `P`

columns are `P1`

, `P2`

, `P3`

, `P4`

, `P5`

, then you could just enter:

`call ols("data.xls", "Q1 ~ P1 + P2 + P3 + P4 + P5");`