topic Re: How can I import two parts from Excel file? in SAS Programming

How can I import two parts from Excel file?

Linlin — Tue, 23 Oct 2012 18:08:41 GMT

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!

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

art297 — Tue, 23 Oct 2012 18:16:40 GMT

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;

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

bnarang — Tue, 23 Oct 2012 18:28:02 GMT

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.

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

Linlin — Tue, 23 Oct 2012 18:29:15 GMT

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?

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

PGStats — Tue, 23 Oct 2012 18:52:22 GMT

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;

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

Linlin — Tue, 23 Oct 2012 19:09:26 GMT

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;

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

art297 — Tue, 23 Oct 2012 21:36:01 GMT

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;

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

Linlin — Wed, 24 Oct 2012 02:13:52 GMT

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;