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
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
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
Hi, I am using SAS9.3. The above code is importing a maximum of 142 columns. Please help. - Thanks in advance.
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.
Try this
Proc import datafile="xx.xls"
dbms=xls replace out=newdata;
getnames=yes;
namerow=3;
datarow=4;
Run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.