topic Re: Proc Import Excel: get column names from different range in SAS Procedures

Proc Import Excel: get column names from different range

fredbell — Wed, 26 Apr 2017 11:53:24 GMT

Hi

How would i go about selecting row 3 as the variable names and then say
row 4 to 11?

PROC IMPORT DATAFILE= "c:\Assurance\Scorecard.xls" DBMS=EXCEL out = TotalSubscribers
replace;
SHEET="'Fobe'";
RANGE='a4:ax11';
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;

RUN;

Fred Message was edited by: fredbell

Re: Proc Import Excel

ArtC — Thu, 16 Dec 2010 18:11:24 GMT

You are very close. Change your range to include the row with the variable names.

Re: Proc Import Excel

fredbell — Thu, 16 Dec 2010 18:34:11 GMT

Thanks but what if i also need to RANGE='a40:ax60';

How do i get Variable names and then define the range?

Fred

Re: Proc Import Excel

ArtC — Thu, 16 Dec 2010 19:21:47 GMT

Do you mean that the variable names are in one range say row three (say a3:aw3), while the data is in another location on the sheet say b40:ax90?

Re: Proc Import Excel

fredbell — Thu, 16 Dec 2010 19:39:25 GMT

Yes that is what i mean, thanks.

Fred

Re: Proc Import Excel: get column names from different range

art297 — Wed, 26 Apr 2017 12:03:32 GMT

Editor's note: this is a popular topic. We've consolidated the best responses in this one Accepted Solution.

From @ArtC, answering for the case where the field names are contiguous with the data range.

You are very close. Change your range to include the row with the variable names.

From @rsva, using the legacy DBMS=XLS method, which supports NAMEROW=:

Proc import datafile="xx.xls"
          dbms=xls replace out=newdata;
         getnames=yes;
         namerow=3;
         datarow=4;
Run;

And from @art297:

Fred,

If the column names are in a completely different (non-contiguous) part of the sheet, then perhaps you need a two-pass approach. One way might be to import the variable names and the data separately. E.g.:

PROC IMPORT OUT= WORK.TEST1
DATAFILE= "c:\xltest.xls"
DBMS=EXCEL2000 REPLACE;
RANGE="A2:C2";
GETNAMES=YES;
RUN;

proc sql noprint;
select catt('F',varnum,'=',name)
into :recodes
separated by ' '
from sashelp.vcolumn
where libname="WORK"
and memname="TEST1"
;
quit;

PROC IMPORT OUT= WORK.TEST2
DATAFILE= "c:\xltest.xls"
DBMS=EXCEL REPLACE;
RANGE="A15:C17";
GETNAMES=NO;
RUN;

data want;
set test2 (rename=(&recodes.));
run;

HTH,
Art

Re: Proc Import Excel

rsva — Wed, 27 Jul 2011 17:35:35 GMT

Try this

Proc import datafile="xx.xls"

dbms=xls replace out=newdata;

getnames=yes;

namerow=3;

datarow=4;

Run;

Re: Proc Import Excel: get column names from different range

soumoboxi — Mon, 08 May 2017 17:28:46 GMT

Hi, I am using SAS9.3. The above code is importing a maximum of 142 columns. Please help. - Thanks in advance.

Re: Proc Import Excel: get column names from different range

ChrisHemedinger — Mon, 08 May 2017 17:46:37 GMT

I think you'll need to provide more details about your Excel file and your code/log...or alternatively, work with SAS Tech Support on your specific case.