BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
geneshackman
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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

geneshackman
Pyrite | Level 9

Reeza, that really worked. Thanks very much.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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