Dear All,
There are graphs mixed in my excel file. I need to create a sas dateset from A1 to Z50 and from A60 to Z100 of the excel file. How can I do that?
Thanks!
Hello!
You could try something like
libname xl Excel "path to your file.xls" GETNAMES=NO;
data whole;
set xl.'Sheet1$A1:Z50'n, xl.'Sheet1$A60:Z100'n;
id='path to your file.xls';
run;
libname xl clear;
PG
Couldn't you just do two imports, using the range option, and then concatenate the two files that you create?
An example of using the range option is:
PROC IMPORT OUT= WORK.PART1
DATAFILE= "c:\xltest.xls"
DBMS=EXCEL2000 REPLACE;
RANGE="A15:C17";
GETNAMES=NO;
RUN;
Thank you Art! That is what I am doing now. I thought maybe we can get the two parts with one pass. Also, I need to add a variable id="name of the excel file". Do I need another data step to get that?
PROC IMPORT OUT= WORK.out
DATAFILE= "C:\Users\admin\Desktop\Book1.xls"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$A1:Z50";
GETNAMES=No;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Same above with changes in the range.
Hello!
You could try something like
libname xl Excel "path to your file.xls" GETNAMES=NO;
data whole;
set xl.'Sheet1$A1:Z50'n, xl.'Sheet1$A60:Z100'n;
id='path to your file.xls';
run;
libname xl clear;
PG
Thank you PG!!! It works great! We need to take out the extra "," in set xl.'Sheet1$A1:Z50'n,, xl.'Sheet1$A60:Z100'n;
Definitely liked PG's solution. If you only want the filename, rather than the full path, you could add to his code to do something like:
libname xl Excel "c:\art\linlintest.xls" GETNAMES=NO;
%let fullpath = %sysfunc(pathname(xl));
data whole;
length filename $50.;
retain filename;
set xl.'Sheet1$A7:c8'n xl.'Sheet1$A14:c15'n INDSNAME=variable;
if _n_ eq 1 then filename=substr("&fullpath.",findc("&fullpath.","\",'b',1)+1);
run;
libname xl clear;
Thank you Art! It turned out that the rows I need are different from file to file. I added something like below after importing the entire file :
data test;
set &dsn;
n=_n_;
data _dull_;
set test;
if upcase(compress(f1))="&cell";
call symputx('c2',_n_);
run;
%let n1=%eval(&c2+1);
%let n2=%eval(&c2+6);
data c2test;
set test;
where n between &n1 and &n2;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.