SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

import excel, wrong rows used for names

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

import excel, wrong rows used for names

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

Accepted Solutions
Solution
‎10-30-2017 11:47 AM
Super User
Posts: 22,838

Re: import excel, wrong rows used for names

Posted in reply to geneshackman

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. 

View solution in original post


All Replies
Super User
Super User
Posts: 9,200

Re: import excel, wrong rows used for names

Posted in reply to geneshackman

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.

Contributor
Posts: 54

Re: import excel, wrong rows used for names

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.

Solution
‎10-30-2017 11:47 AM
Super User
Posts: 22,838

Re: import excel, wrong rows used for names

Posted in reply to geneshackman

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. 

Contributor
Posts: 54

Re: import excel, wrong rows used for names

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

Contributor
Posts: 54

Re: import excel, wrong rows used for names

Reeza, that really worked. Thanks very much.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 201 views
  • 1 like
  • 3 in conversation