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 |
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.
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.
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.
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.
Reeza, that seems to work. I will try some more to check. Thanks!
Reeza, that really worked. Thanks very much.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.