import excel, wrong rows used for names

[geneshackman]

I am importing an excel file that we get from another group, using this code

libname one "\\scchoit40\phig\gxs03\chire\";

proc import out = preg_1

datafile = "\\scchoit40\phig\gxs03\chire\testdata.xlsx"

DBMS=XLSX REPLACE;

sheet = "2012-14";

RANGE = "A5:G67";

getnames = yes;

run;

proc import out = preg_2

datafile = "\\scchoit40\phig\gxs03\chire\testing_2010_2014.xlsx"

DBMS=XLSX REPLACE;

sheet = "Total_2012_2014";

RANGE = "A6:G68";

getnames = yes;

run;

The first couple of lines of the testing data set look like this, below. But the sas program reads the first line of the file as the labels, rather than the first line of data (starting at row a6). Doesn't the range tell sas where to start reading the data, so shouldn't the first line of the data range be the lables?

New York State testdata by County of Residence
2011-2013
County	White NH	Black NH	Asian NH	Other NH	Hispanic	Total
ALBANY	104	127	2	19	48	300
ALLEGANY	40	0	0	2	1	43
BROOME	146	39	4	17	14	220
CATTARAUGUS	85	3	1	12	5	106

[RW9]

When creating a SAS dataset, there needs to be some name given to the variables.  You can try:

getnames=no;

However you will end up with data where all the variables are called COLx.

Recommendation, use an appropriate data transfer medium (CSV, XML) and write a proper datastep import program which will apply all of the correct names, labels, formats, informats per your import agreement.  Otherwise you will end up with garbage data format + guessing import process = garbage out.

[geneshackman]

Not clear what  you mean "When creating a SAS dataset, there needs to be some name given to the variables". The range does have variable names, in the first row of the data range. That is my question: why isn't getnames=yes reading the column names from row 6, instead of from row 1.

[Reeza · Accepted solution]

I don't think it works as expected when you use both SHEET and RANGE. Try specifying only RANGE, which can include the SHEET name and see how that works. I do recall that this is actually noted in the documentation somewhere but I don't have time to search for the reference at the moment. 

[geneshackman]

Reeza, that seems to work. I will try some more to check. Thanks!

[geneshackman]

Reeza, that really worked. Thanks very much.