topic Proc Import Excel creating a table per tab in SAS Programming

Proc Import Excel creating a table per tab

jerry898969 — Wed, 14 Sep 2011 13:27:45 GMT

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

Proc Import Excel creating a table per tab

art297 — Wed, 14 Sep 2011 13:36:04 GMT

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 ;

Proc Import Excel creating a table per tab

jerry898969 — Wed, 14 Sep 2011 14:06:40 GMT

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

Re: Proc Import Excel creating a table per tab

FriedEgg — Wed, 14 Sep 2011 20:30:20 GMT

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;