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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.