DATA Step, Macro, Functions and more

How can I import two parts from Excel file?

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

How can I import two parts from Excel file?

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!


Accepted Solutions
Solution
‎10-23-2012 02:52 PM
Respected Advisor
Posts: 4,651

Re: How can I import two parts from Excel file?

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

PG

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: How can I import two parts from Excel file?

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;

Super Contributor
Posts: 1,636

Re: How can I import two parts from Excel file?

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?

Frequent Contributor
Posts: 86

Re: How can I import two parts from Excel file?

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.

Solution
‎10-23-2012 02:52 PM
Respected Advisor
Posts: 4,651

Re: How can I import two parts from Excel file?

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

PG
Super Contributor
Posts: 1,636

Re: How can I import two parts from Excel file?

Thank you PG!!! It works great!Smiley Happy   We need to take out the extra "," in set xl.'Sheet1$A1:Z50'n,, xl.'Sheet1$A60:Z100'n;

PROC Star
Posts: 7,363

Re: How can I import two parts from Excel file?

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;

Super Contributor
Posts: 1,636

Re: How can I import two parts from Excel file?

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 300 views
  • 7 likes
  • 4 in conversation