PROC IMPORT and GUESSINGROWS=

[r***@yahoo.fr]

Hello everyone,
I just started using SAS and after reading the Little SAS book I am
finally getting my hands dirty.

I am trying to import a 2003 excel file (255 columns) composed of a
set of 254 stocks with weekly returns and the associated dates in the
first column. At the top row column represent stocks' names (254
stocks, the first column is used for the date). It has blank rows for
unavailable returns and weekly returns in number form when the data is
available (altough specified as type : general in excel properties).

I am currently having two issues with this import procedure:

1. it misses the observations in the excel file
2. it is not using the appropriate datatype (CHARACTER instead of
NUMBER)

stocks returns in the SAS data set (in the ensuing rows) are left
blank except for stocks which have data available right at the
beginning of the date range (most stocks start trading after this date
therefore leaving a number of blank rows)

I have tried changing the GUESSINGROWS property through regedit but
the import statement still leave out tons of data. As I found out
GUESSINGROWS only check for variable attributes and not the data
itself.

Furthermore, I am wondering why SAS is not recognizing the returns
serie of each stock as number data type (for the stock which were
correctly imported the format is BEST12.).

I would really appreciate it if someone had any tips regarding those
issues

thank you

[r***@yahoo.fr]

Actually I found out what was wrong.

Basically The rows consisting of stock returns were not set as a
number datatype under excel but as general.
After changing all the rows to the number datatype, everything
imported smoothly using the sas import statement.
The returns are no longer recorded as CHARACTER data type but as
NUMERIC and a dot appears for the missing values as I was expecting
while before I had blanks

[Joe Matise]

There are a host of options. Typically changing the TYPEGUESSROWS option in
the registry for the JET engine will fix most problems; if that's what
you've already tried [and are sure you did it correctly], then the easiest
is probably either MIXED=YES (which will force numerics to be brought in as
text in a column that is mixed] or Art's solution depending on the specifics
of your project.

If that doesn't work for some reason, you could always try the ACCESS method
[ http://support.sas.com/onlinedoc/913/getDoc/en/acpcref.hlp/a000214470.htm],
which has a slightly different set of options [I think ACCESS is how
dbms=xls works in PROC IMPORT?]. Changing to DBMS=XLS often works when
DBMS=EXCEL does not.

Finally, there is in the libname engine the DBSASTYPE option, which allows
you to specify the type of each variable individually; a LOT of work for
many columns but it is an option.

-Joe

Post by r***@yahoo.fr
Hello everyone,
I just started using SAS and after reading the Little SAS book I am
finally getting my hands dirty.
I am trying to import a 2003 excel file (255 columns) composed of a
set of 254 stocks with weekly returns and the associated dates in the
first column. At the top row column represent stocks' names (254
stocks, the first column is used for the date). It has blank rows for
unavailable returns and weekly returns in number form when the data is
available (altough specified as type : general in excel properties).
1. it misses the observations in the excel file
2. it is not using the appropriate datatype (CHARACTER instead of
NUMBER)
stocks returns in the SAS data set (in the ensuing rows) are left
blank except for stocks which have data available right at the
beginning of the date range (most stocks start trading after this date
therefore leaving a number of blank rows)
I have tried changing the GUESSINGROWS property through regedit but
the import statement still leave out tons of data. As I found out
GUESSINGROWS only check for variable attributes and not the data
itself.
Furthermore, I am wondering why SAS is not recognizing the returns
serie of each stock as number data type (for the stock which were
correctly imported the format is BEST12.).
I would really appreciate it if someone had any tips regarding those
issues
thank you

[Arthur Tabachneck]

The method I use is shown in the post at:
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0802b&L=sas-l&D=1&O=A&P=15595

Art
---------

Post by r***@yahoo.fr
Hello everyone,
I just started using SAS and after reading the Little SAS book I am
finally getting my hands dirty.
I am trying to import a 2003 excel file (255 columns) composed of a
set of 254 stocks with weekly returns and the associated dates in the
first column. At the top row column represent stocks' names (254
stocks, the first column is used for the date). It has blank rows for
unavailable returns and weekly returns in number form when the data is
available (altough specified as type : general in excel properties).
1. it misses the observations in the excel file
2. it is not using the appropriate datatype (CHARACTER instead of
NUMBER)
stocks returns in the SAS data set (in the ensuing rows) are left
blank except for stocks which have data available right at the
beginning of the date range (most stocks start trading after this date
therefore leaving a number of blank rows)
I have tried changing the GUESSINGROWS property through regedit but
the import statement still leave out tons of data. As I found out
GUESSINGROWS only check for variable attributes and not the data
itself.
Furthermore, I am wondering why SAS is not recognizing the returns
serie of each stock as number data type (for the stock which were
correctly imported the format is BEST12.).
I would really appreciate it if someone had any tips regarding those
issues
thank you