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
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 ;
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 ;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.