PROC IMPORT procedure in EXCEL

[wongmay]

Dear expert,
    Recently, when I tried to import an EXCEL file into SAS dataset, I found that the variable names in the output dataset were not displayed correctly.  Could any expert please correct my concepts ?

Problem :
An EXCEL 2003 file with variable names, namely NAME, EXAM & SCORE in columns A, B and C.  A new column showing the average total was also computed in column D.

EXCEL 2003 (sheet="Name" in NAME.xls)
-------------------------------------
Col A   Col B   Col C   Col D
name    exam    score   69.5
David      1       60
David      2        5
David      3       75
Kitty      1       70
Kitty      2       85
Mary       1       25
Mary       2       90

Mary       3       85
Mary       4      100

Mary       5      100

When I submitted the PROC IMPORT below, the output dataset variable names were A, B, C and D (blank column) even though PROC IMPORT OUT=work.tt99 (DROP=D) or GETNAMES=YES were both added in the syntax.  However, when I deleted the column D in the EXCEL file, the variable names were displayed correctly.

proc import out      = work.tt99
            datafile = "d:\name.xls"
            DBMS     = xls replace;
            sheet    = "name";
            getnames = YES;
run;

or

proc import out      = work.tt99 (drop=D)
            datafile = "d:\name.xls"
            DBMS     = xls replace;
            sheet    = "name";
            getnames = YES;
run;

[RW9]

Hi,

Simple really.  SAS has specific rules regarding naming convention of variables.  In your column D in the Excel grid you have the Number 69.5, this is no way a valid column name in SAS hence the import procedure removes the getnames statement and reads the data in from row 2, with default column names.  If you want to import data like that then you would need to save as CSV and write the import datastep yourself, or move that number down a row.

[Ksharp]

You can use RANGE to exclude the last column .

proc import out      = work.tt99

            datafile = "c:\temp\name.xls"

            DBMS     = xls replace;

            range    = "name$A1:C11";

            getnames = YES;

run;

Xia Keshan

[wongmay]

Thank you all of you.

Actually, the excel file may contain data in columns A-F and we don't know the total number of data rows before.  Could I write this ?

proc import out      = work.tt99

            datafile = "c:\temp\name.xls"

            DBMS     = xls replace;

            range    = "name$A:F";

            getnames = YES;

run;

regards

May Wong

[RW9]

You could, but then you would end up with a dataset with 256 columns.  Really, if you are importing data from third party you need to have an agreement of what data would be sent, in what format, and agreed on both sides.  Otherwise you will run into trouble each time you import a File.  For instance this run I call the sheet xyz, next time I call it yzx.  Sometimes I put only number data in X, next time I put NA etc.  Excel is not a tool for the transfer of data, hence it is doubly important to agree up front what the structure (as that is the main is) is. 

[Ksharp]

why not write a very large number for row ?

"name$A1:F32456";

Wow , I can't believe my eye , you can use

"name$A:F";

Message was edited by: xia keshan

[wongmay]

It's great.  Thanks all of you.