Help using Base SAS procedures

Proc Import Excel: get column names from different range

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Proc Import Excel: get column names from different range

[ Edited ]

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


Accepted Solutions
Solution
‎04-26-2017 08:03 AM
PROC Star
Posts: 7,363

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

[ Edited ]

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

View solution in original post


All Replies
Valued Guide
Posts: 632

Re: Proc Import Excel

You are very close. Change your range to include the row with the variable names.
Contributor
Posts: 73

Re: Proc Import Excel

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

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

Fred
Valued Guide
Posts: 632

Re: Proc Import Excel

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?
Contributor
Posts: 73

Re: Proc Import Excel

Yes that is what i mean, thanks.

Fred
Solution
‎04-26-2017 08:03 AM
PROC Star
Posts: 7,363

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

[ Edited ]

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

Occasional Learner
Posts: 1

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

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

Community Manager
Posts: 2,768

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

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.

Contributor
Posts: 38

Re: Proc Import Excel

Try this

Proc import datafile="xx.xls"

          dbms=xls replace out=newdata;

         getnames=yes;

         namerow=3;

         datarow=4;

Run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 26736 views
  • 0 likes
  • 6 in conversation