topic Re: PROC IMPORT procedure in EXCEL in SAS Programming

PROC IMPORT procedure in EXCEL

wongmay — Tue, 08 Jul 2014 09:43:27 GMT

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;

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

Re: PROC IMPORT procedure in EXCEL

RW9 — Tue, 08 Jul 2014 10:03:03 GMT

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.

Re: PROC IMPORT procedure in EXCEL

Ksharp — Tue, 08 Jul 2014 14:34:47 GMT

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

Re: PROC IMPORT procedure in EXCEL

wongmay — Tue, 08 Jul 2014 14:56:28 GMT

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

Re: PROC IMPORT procedure in EXCEL

RW9 — Tue, 08 Jul 2014 15:12:05 GMT

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.

Re: PROC IMPORT procedure in EXCEL

Ksharp — Tue, 08 Jul 2014 15:16:02 GMT

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

Re: PROC IMPORT procedure in EXCEL

wongmay — Tue, 08 Jul 2014 15:21:51 GMT

It's great. Thanks all of you.