DATA Step, Macro, Functions and more

Proc Import Excel creating a table per tab

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

Proc Import Excel creating a table per tab

I have a excel file that is sent to me that I need to bring into sas.  This is what i'm currently doing

%let prjdir = c:\temp ;

data tabs ;   

    input tabname $ ;   

cards;

Tab1

Tab2

OTHER SPECS

;

run ;

data _null_ ;

    set tabs end=last ;

    call symput('tabname'||strip(_n_), strip(tabname) ) ;

    if last then call symput('ntabs', strip(_n_) ) ;

run ;

%put &ntabs ;

%macro import_tabs ;

%do i= 1 %to &ntabs ;

proc import out = raw_&&tabname&i

    datafile= "&prjdir.\temp.xls"

    dbms = excel

    replace

    ;

    sheet="&&tabname&i" ;

    getnames = yes ;         

run ;

%end ;

%mend import_tabs ;

options nomprint ;

%import_tabs ;

My problem is the last tab name gives me this error

ERROR: File _IMEX_.'OTHER$'n.DATA does not exist.

ERROR: Import unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.46 seconds

      cpu time            0.18 seconds

When I shorten that tab name to OtherSP it works fine.

Is there a way to cycle through the tabs without needing to use the tab names?  I can't control the names that are used.

Thank you


Accepted Solutions
Solution
‎09-14-2011 09:36 AM
PROC Star
Posts: 7,363

Proc Import Excel creating a table per tab

I haven't checked the rest of your code, but you have a problem with the initial file.  You don't extend the length of the tabname variable so that it can handle more than 8 characters, and you don't account for the space in the value.  Try:

data tabs ;

   length tabname $15;

    input tabname $ &;

cards;

Tab1

Tab2

OTHER SPECS

;

run ;

View solution in original post


All Replies
Solution
‎09-14-2011 09:36 AM
PROC Star
Posts: 7,363

Proc Import Excel creating a table per tab

I haven't checked the rest of your code, but you have a problem with the initial file.  You don't extend the length of the tabname variable so that it can handle more than 8 characters, and you don't account for the space in the value.  Try:

data tabs ;

   length tabname $15;

    input tabname $ &;

cards;

Tab1

Tab2

OTHER SPECS

;

run ;

Super Contributor
Posts: 398

Proc Import Excel creating a table per tab

Art,

Thank you so much.  That put me on the right track.  I was also using the tab name as part of my data table name which with the space doesn't work,  Instead I'm using the loop counter to add to my raw_ prefix.

Thanks so much

Trusted Advisor
Posts: 1,300

Re: Proc Import Excel creating a table per tab

You can do it all in a single step too:

data tabs;

  input tabname $20.;

  call execute('proc import out=raw_' || strip(tabname) || ' datafile="C:\temp\' || strip(tabname) || '" dbms=excel replace; sheet="' || strip(tabname) || '"; getnames=yes; run;');

  cards;

Tab1

Tab2

OTHER SPECS

;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 287 views
  • 0 likes
  • 3 in conversation