topic Re: Import Excel file in SAS using libname statment in SAS Programming

Import Excel file in SAS using libname statment

MohammedNaeem — Wed, 16 Oct 2019 11:46:52 GMT

Dear all,

I am trying to import excel file using libname statement but in my dataset variable name start from second row, actual data from third row and data in first row is of no use. How can I set getname from 2nd row and getdata from 3rd row like proc import.

I am trying to this done using following code:

libname myexcel pcfiles path='\BA1.xlsx'
		 scantime=yes stringdates=no dbmax_text=2000 ;

data dv;
	set myexcel.'DV$'n;
run;

Not Using proc import because format of data is getting change.

Re: Import Excel file in SAS using libname statment

Patrick — Wed, 16 Oct 2019 11:55:31 GMT

The libname XLSX engine gives you access to a whole Excel Workbook and you can't define special options on a sheet level.

I don't understand why you can't use Proc Import. If the source structure changes then that's impacting both the result Proc Import and the XLSX engine will return.

Re: Import Excel file in SAS using libname statment

Ksharp — Wed, 16 Oct 2019 13:15:07 GMT

Not sure. Try option DATAROW=3 .

Re: Import Excel file in SAS using libname statment

Patrick — Wed, 16 Oct 2019 13:19:49 GMT

@Ksharp wrote:

Not sure. Try option DATAROW=3 .

@Ksharp

That works for Proc Import but not for the Libname XLSX engine as documented here