Proc Import Excel: get column names from different range

[fredbell]

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

[ArtC]

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

[fredbell]

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

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

Fred

[ArtC]

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?

[fredbell]

Yes that is what i mean, thanks.

Fred

[art297 · Accepted solution]

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

[soumoboxi]

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

[ChrisHemedinger]

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.

[rsva]

Try this

Proc import datafile="xx.xls"

          dbms=xls replace out=newdata;

         getnames=yes;

         namerow=3;

         datarow=4;

Run;