topic import excel, wrong rows used for names in SAS Data Management

import excel, wrong rows used for names

geneshackman — Mon, 30 Oct 2017 14:56:13 GMT

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

Re: import excel, wrong rows used for names

RW9 — Mon, 30 Oct 2017 15:06:29 GMT

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.

Re: import excel, wrong rows used for names

geneshackman — Mon, 30 Oct 2017 15:13:42 GMT

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.

Re: import excel, wrong rows used for names

Reeza — Mon, 30 Oct 2017 15:13:46 GMT

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.

Re: import excel, wrong rows used for names

geneshackman — Mon, 30 Oct 2017 15:21:32 GMT

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

Re: import excel, wrong rows used for names

geneshackman — Mon, 30 Oct 2017 15:48:27 GMT

Reeza, that really worked. Thanks very much.