BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Linlin
Lapis Lazuli | Level 10

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
art297
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Opal | Level 21

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
Linlin
Lapis Lazuli | Level 10

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;

art297
Opal | Level 21

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
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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