How can I import two parts from Excel file?

[Linlin]

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!

[art297]

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;

[Linlin]

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?

[bnarang]

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.

[PGStats · Accepted solution]

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

[Linlin]

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;

[art297]

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;

[Linlin]

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;