topic Re: Proc Import Excel File with data in second row in SAS Programming

Proc Import Excel File with data in second row

GenDemo — Thu, 29 Dec 2016 02:21:43 GMT

Hi All

I have an Excel file that I need to import from another team to SAS. Usually I would save it as a CSV and import using a data step with an infile statement. But for various reasons i don't want to do that for this particular excersice - I need to import the Excel file directly. Please see attached an example of the file structure. The first row is a heading that I want to skip, with the variable names in the second row and the data from there on.

I looked on the forum and for some reason the code that people suggest to do this, gives me an error. When I try and run the code below

proc import datafile= "C:\temp\book3b.xlsx"
	out= Test_Data replace
	DBMS= Excel;
	sheet= "Test Data";
/*	startrow=3;*/
/*	namerow=2;*/
	getnames= yes;
/*	mixed=yes;*/
/*	scantext=yes;*/
/*	usedate=yes;*/
	datarow=3;
 run;

I get "ERROR 180-322: Statement is not valid or it is used out of proper order." on the 'datarow' line.

1) How can I ignore the first row?

2) is there a way i can tell the import procedure I want to exclude certain columns? The 'Comments' column has a high potential to give errors and inconsistancies, and as such i would just like to ignore it.

Thnx

Re: Proc Import Excel File with data in second row

Reeza — Thu, 29 Dec 2016 03:00:29 GMT

Try DBMS=XLSX and DATAROW=3.

I couldn't get it to work with GETNAMES and DATAROW though.

Re: Proc Import Excel File with data in second row

Reeza — Thu, 29 Dec 2016 03:04:09 GMT

Helpful little note in the documentation, which did work as expected.

https://support.sas.com/documentation/cdl/en/acpcref/69731/HTML/default/viewer.htm#n0msy4hy1so0ren1acm90iijxn8j.htm

Specify RANGE="Sheetname$A#:0"; where # is the first data row. Thus, RANGE="sheet1$A3:0"; starts to read the data at row 3. If you use RANGE= for this purpose, do not specify the DATAROW= statement.

proc import out=test datafile='/folders/myfolders/input.xlsx' dbms=xlsx replace; getnames=yes;
range="Sheet1$A2:0";
run;

Re: Proc Import Excel File with data in second row

GenDemo — Tue, 03 Jan 2017 03:45:02 GMT

Nope, its still not working. It doesn't matter what I do, it keeps on taking the first row (the one I want to ignore) as the variable names. Log window gives no errors or notes. The range statement makes no difference. If I comment out the range stament with the datarow statement in, I can either get the variable names in as the first row, or exclude it.

proc import datafile= "C:\temp\book3b.xlsx"

out= Test_Data replace

DBMS= XLSX;

sheet= "Test Data";

getnames= yes;

/* datarow=2;*/

range="Test Data$A2:0";

run;

So I can go the log way around and import it like this with the variable names in the first row, and then somehow create a new dataset from this....

Re: Proc Import Excel File with data in second row

GenDemo — Tue, 03 Jan 2017 04:02:17 GMT

ah...the sheet statement with the range statement confuses the thing. Silly that it doesn't give you an error or a note on this.

proc import datafile= "C:\temp\book3b.xlsx"

out= Test_Data replace

DBMS= XLSX;

/* sheet= "Test Data";*/

getnames= yes;

/* datarow=2;*/

range="Test Data$A2:0";

run;