Importing data in SAS

[Rajnish]

Hi All,

I  have been struggling to import a .xlsx file in SAS. I will explain my problem as clearly as possible. Its a very small data but the problem is for few variables there are no observations till row number 25 or more. When I am importing this file using the import wizard, the data set that it creates has some variable in the character format and other variables in the numeric format, though all the variables are in numeric format. So when I am transposing it using a particular identifier, it transposes on the numeric variables and other observations are lost. I have even tried GUESSINGROWS=200 but its of no help. The other way is I can write the entire program using informat, format and input but I don't want to use this because there are many xlsx files for which I won't know number of variables and i find it really tedious. Please suggest a sure shot solution, so that SAS takes all the variables as numeric. Help will be highly appreciated. Thanks in advance

[PGStats]

The problem is with Excel which only scans 8 lines of data to determine the data type of columns. To change that behavior you must change the TypeGuessRows entry in the Windows registry. You only need to do this once. Read instructions here:

SAS/ACCESS(R) 9.3 Interface to PC Files: Reference

PG

[Rajnish]

Thanks for the help but after changing the TypeGuessRows=0, I am encountered with another problem. Now SAS is not even reading that particular column and observation is getting delete. I have attached the Data file this time. need to read to both sheet, one with date and another with other sheet and then I have merge it after taking the transpose of it.

[PGStats]

I can read your data correctly with:

libname xl Excel "&sasforum\datasets\Rajnish.xls";

proc contents data=xl.'Date_m$'n; run;

with result:

                                   The SAS System   17:02 Tuesday, June 11, 2013   1

                               The CONTENTS Procedure

           Data Set Name        XL.'Date_m$'n    Observations          .
           Member Type          DATA             Variables             13
           Engine               EXCEL            Indexes               0
           Created              .                Observation Length    0
           Last Modified        .                Deleted Observations  0
           Protection                            Compressed            NO
           Data Set Type                         Sorted                NO
           Label
           Data Representation  Default
           Encoding             Default

                    Alphabetic List of Variables and Attributes

          #    Variable     Type    Len    Format    Informat    Label

          2    Date         Num       8    DATE9.    DATE9.      Date
          3    Date1        Num       8    DATE9.    DATE9.      Date1
          4    Date2        Num       8    DATE9.    DATE9.      Date2
          5    Date3        Num       8    DATE9.    DATE9.      Date3
          6    Date4        Num       8    DATE9.    DATE9.      Date4
          7    Date5        Num       8    DATE9.    DATE9.      Date5
          8    Date6        Num       8    DATE9.    DATE9.      Date6
          9    Date7        Num       8    DATE9.    DATE9.      Date7
         10    Date8        Num       8    DATE9.    DATE9.      Date8
         11    Date9        Num       8    DATE9.    DATE9.      Date9
         12    Date10       Num       8    DATE9.    DATE9.      Date10
         13    Date11       Num       8    DATE9.    DATE9.      Date11
          1    ISIN_code    Char     12    $12.      $12.        ISIN_code

Please try the same thing.

PG

[Rajnish]

Thanks for you help but the problem is with the data sheet titled EPS_M. I am also able to import Date_M but while importing EPS_M, I am having problem. I noticed two things: first, in excel when I selected EPS (the second column) and asked for its format, there was no format, whereas when I selected those columns of excel which are imported correctly, they have general format. So I changed all the columns to general format and then tried importing it in SAS but again the problem remained. Very perplexing.

[PGStats · Accepted solution]

I don't see what's wrong with the following:

                               The CONTENTS Procedure

           Data Set Name        XL.'Eps_m$'n    Observations          .
           Member Type          DATA            Variables             13
           Engine               EXCEL           Indexes               0
           Created              .               Observation Length    0
           Last Modified        .               Deleted Observations  0
           Protection                           Compressed            NO
           Data Set Type                        Sorted                NO
           Label
           Data Representation  Default
           Encoding             Default

                    Alphabetic List of Variables and Attributes

          #    Variable     Type    Len    Format    Informat    Label

          2    EPS          Num       8                          EPS
          3    EPS1         Num       8                          EPS1
          4    EPS2         Num       8                          EPS2
          5    EPS3         Num       8                          EPS3
          6    EPS4         Num       8                          EPS4
          7    EPS5         Num       8                          EPS5
          8    EPS6         Num       8                          EPS6
          9    EPS7         Num       8                          EPS7
         10    EPS8         Num       8                          EPS8
         11    EPS9         Num       8                          EPS9
         12    EPS10        Num       8                          EPS10
         13    EPS11        Num       8                          EPS11
          1    ISIN_code    Char     12    $12.      $12.        ISIN_code

I checked the number of non-missing cells and they match at 472.

PG

[Rajnish]

Thanks a lot. I managed to import the file correctly but I would like to inform you that I imported the data correctly using SAS Enterprises and not SAS 9.3. I don't know why I was not able to import it using SAS 9.3 but thanks a lot for your help.