DATA Step, Macro, Functions and more

PROC IMPORT procedure in EXCEL

Reply
Occasional Contributor
Posts: 16

PROC IMPORT procedure in EXCEL

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;

Super User
Super User
Posts: 7,970

Re: PROC IMPORT procedure in EXCEL

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.

Super User
Posts: 10,035

Re: PROC IMPORT procedure in EXCEL

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

Occasional Contributor
Posts: 16

Re: PROC IMPORT procedure in EXCEL

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

Super User
Super User
Posts: 7,970

Re: PROC IMPORT procedure in EXCEL

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. 

Super User
Posts: 10,035

Re: PROC IMPORT procedure in EXCEL

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

Occasional Contributor
Posts: 16

Re: PROC IMPORT procedure in EXCEL

It's great.  Thanks all of you.

Ask a Question
Discussion stats
  • 6 replies
  • 3057 views
  • 0 likes
  • 3 in conversation