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;
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.
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.