Hello everyone,
I have a question regarding how to keep the excel sheet name as a variable.
Here is the code I used to import multiple excel sheets in SAS
%macro excelbook;
libname D 'F:\phdsas\Book1.xls';
proc sql noprint;
select count (distinct (memname)) INTO :TOT
from sashelp.vtable
where libname='D' ;
SELECT DISTINCT (memname) into :s1-:s%trim(%left(&tot))
from sashelp.vtable
where libname='D';
select distinct(compress(MEMNAME,"'$")) into: v1 - :v%trim(%left(&tot))
from sashelp.vtable
where libname='D';
quit;
libname D clear;
%DO I=4 %TO &tot;
%put &&v&i;
%put &&s&i;
proc import DATAfile= 'F:\phdsas\Book1.xls' out=&&v&I replace;
RANGE="&&s&i";
getnames=NO;
run;
DATA &&v&I;
SET &&v&I;
CODE="&&v&I";
RUN;
proc append base=all data=&&v&I force;
run;
%END;
%mend;
%excelbook;
I wanted to keep the sheet name as a variable called "ticker".
Thank you in advance for your help.
You already have the value in a macro variable just create a new variable to hold the value.
Note that you are working too hard to pull the information into macro variables, you can create them and count them in one step.
Why does the DO loop start at 4 ???
%macro excelbook(path);
libname d &path ;
proc sql noprint;
select memname
, compress(memname,"'$"))
into :s1-:s9999
, :v1-:v9999
from sashelp.vtable
where libname='D'
;
%let tot=&sqlobs;
quit;
libname d clear;
%do i=4 %to &tot;
%put SHEET=&&s&i CODE=&&v&i;
proc import datafile=&path out=&&v&i replace;
range="&&s&i";
getnames=no;
run;
data &&v&i;
length sheet code $32 ;
sheet="&&s&i";
code="&&v&i";
set &&v&i;
run;
proc append base=all data=&&v&i force;
run;
%end;
%mend excelbook;
%excelbook('f:\phdsas\book1.xls');
Perhaps rather than import use the libname to import the dataset and the indsname option?
libname D 'F:\phdsas\Book1.xls';
data want;
set d.'sheetname$'n indsname=source;
Sheet_Name=source;
run;
Isn't v1-vn your sheet name stored in code?
Yes.
In my excel book, the sheet name is the ticker code. So I was wondering if there were a way for me to retain that as a variable.
Thank you for your help.
Thanks Reeza,
I will try that and let you know how it goes.
Thank you for your help!
I'm confused. Reeza pointed out that you already capture sheet name with a variable called code. Thus, if you just changed the line:
CODE="&&v&I";
to
ticker="&&v&I";
I think you would accomplish what you want to do.
Hi everyone,
Thank you all for your assistance.
Now it is not working at all.
The code worked just fine when I have the sheet numbered, but when I am using sheet names with tickers, it is not working at all.
Here is the error code it brings back when I run the code
For example sheet "AMZG US EQUITY":
1 AMZG US Equity
--
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION,
BASE, CREATE, DATA, FORCE, GETSORT, NEW, NOWARN, OUT.
Is there a problem with the sheet name?
Post your full code/log
You have to account for the spaces in your sheet names. That requires changing one line of your code. Try:
select distinct(translate(trim(compress(MEMNAME,"'$")),'_',' ')) into: v1 - :v%trim(%left(&tot))
instead of:
select distinct(compress(MEMNAME,"'$")) into: v1 - :v%trim(%left(&tot))
Hello Arthur,
I tried changing the compress(NEMNAME ... bit
My full code is now:
%macro excelbook;
libname D 'F:\phdsas\Book1.xls';
proc sql noprint;
select count (distinct (memname)) INTO :TOT
from sashelp.vtable
where libname='D' ;
SELECT DISTINCT (memname) into :s1-:s%trim(%left(&tot))
from sashelp.vtable
where libname='D';
select distinct(translate(trim(compress(MEMNAME,"'$")),'_',' ')) into: v1 - :v%trim(%left(&tot))
from sashelp.vtable
where libname='D';
quit;
libname D clear;
%DO I=4 %TO &tot;
%put &&v&i;
%put &&s&i;
proc import DATAfile= 'F:\phdsas\Book1.xls' out=&&v&I replace;
RANGE="&&s&i";
getnames=NO;
run;
DATA &&v&I;
SET &&v&I;
TICKER="&&v&I";
RUN;
proc append base=all data=&&v&I force;
run;
%END;
%mend;
%excelbook;
The error message I got back is:
WARNING: Apparent symbolic reference TOT not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: &tot
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro EXCELBOOK will stop executing.
Does the xls file still exist or did you change its name or extenstion?
The only difference between your and my code is that I included the engine in my libname statement i.e.:
libname D excel 'c:\phdsas\Book1.xls';
However, since it was working for you earlier without declaring the excel engine, I didn't see any reason to include it now.
The only thing I can suggest is to ensure that the excel workbook is still in the specified directory, still has an xls extension, close and start a new SAS session and try running the macro without making any other changes.
Oh yes it is working now. I had the excel file open so that's probably why I got an error message.
Thanks
You already have the value in a macro variable just create a new variable to hold the value.
Note that you are working too hard to pull the information into macro variables, you can create them and count them in one step.
Why does the DO loop start at 4 ???
%macro excelbook(path);
libname d &path ;
proc sql noprint;
select memname
, compress(memname,"'$"))
into :s1-:s9999
, :v1-:v9999
from sashelp.vtable
where libname='D'
;
%let tot=&sqlobs;
quit;
libname d clear;
%do i=4 %to &tot;
%put SHEET=&&s&i CODE=&&v&i;
proc import datafile=&path out=&&v&i replace;
range="&&s&i";
getnames=no;
run;
data &&v&i;
length sheet code $32 ;
sheet="&&s&i";
code="&&v&i";
set &&v&i;
run;
proc append base=all data=&&v&i force;
run;
%end;
%mend excelbook;
%excelbook('f:\phdsas\book1.xls');
Hello Tom,
Thanks for your help.
I ran the code you gave me and here is the error message.
For example for sheet DPW US EQUITY:
DPW US Equity
--
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION,
BASE, CREATE, DATA, FORCE, GETSORT, NEW, NOWARN, OUT.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
The do loop starts from 4 because my data is in sheet 4 onward.
Should I change the
select memname
, compress(memname,"'$")
into :s1-:s9999
?
You are not removing the spaces in the sheetnames when you are generating the dataset names.
You need to either add space to the list of characters to compress out of the sheetnames or convert the spaces to other valid character like an underscore.
compress(memname,"'""$ ")
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.