BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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 ;

jerry898969
Pyrite | Level 9

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

FriedEgg
SAS Employee

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;

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
  • 3 replies
  • 919 views
  • 0 likes
  • 3 in conversation