BookmarkSubscribeRSS Feed
dl0324
Fluorite | Level 6

I'm creating a macro that recursively imports multiple excel files that have multiple sheets within each excel file. Some sheet names contain special characters (i.e. ampersand, % sign) so I want to incorporate %superQ() in my importing macro to mask these special characters. But when I run I get an error: invalid symbolic variable name 'value'. Can someone please let me know what I'm doing wrong? 😕 

 

Below is an extract of my macro : 

%macro imp;
 data _null_;
   set file_directory (where=(prxmatch('/(xls|csv)/i',FileType)));
   call symput('path'||left(_n_),strip(path));
   call symput('filen'||left(_n_),strip(filename));
   call symput('table'||left(_n_),cats("File",(_n_)));
   call symput('type'||left(_n_),strip(upcase(filetype)));
   call symput('stop',strip(_n_));
 run;

%do i=1 %to &stop.; /* Enter into excel library to extract sheetnames from each file. */ %if %substr(&&type&i,1,3) eq XLS %then %do; libname excellib excel "&&path&i.\&&filen&i"; proc sql noprint; create table sheetname&i. as /* Return a distinct list of sheetnames from each file. */ select distinct Path, Filename, SheetName from (select "&&path&i." as Path, "&&filen&i" as FileName, case when prxmatch('/(_xlnm#)/i',memname) then tranwrd(compress(substr(memname,1,index(memname,'_xlnm#')-1),"$",""),"''","'") else tranwrd(compress(memname,"$",""),"''", "'") end as SheetName from sashelp.vstabvw where libname="EXCELLIB");
/* Assign macros to sheet names that are to be imported. */ select count(DISTINCT sheetname) into :cnt_sht from sheetname&i.; select DISTINCT sheetname into :sheet1 - :sheet%left(&cnt_sht) from sheetname&i.; quit;
libname excellib clear;
do j=1 %to &cnt_sht; /* Loop through to import multiple sheets per excel file. */ proc import datafile="&&path&i.\&&filen&i" out=&&table&i.._SH&j. replace; /* Each output is named with a suffix _SH1 to n (=maximum number of sheets found). */ sheet="%superq(&&sheet&j)"; /*this is where i get an error*/ getnames=yes; mixed=yes; scantext=yes; run; %end;
%mend;

 

4 REPLIES 4
Reeza
Super User

I dont have an answer for your question but that seems overly complex.

 

If you can assign a libname can you use proc copy instead. Or can you use NLITERAL() to read in sheet names.

BrunoMueller
SAS Super FREQ

Hi

 

Check the doc for the %SUPERQ function, you must specify the name of a macro var without the ampercent.

 

However you can use an &suffix, see also code below

 

%let sheet1 = %nrstr(cars&more);
%let sheet2 = %nrstr(others&more);

%let i = 1;

%put NOTE: *%superq(sheet&i)*;

proc import
  datafile="c:\temp\cars.xlsx" 
  out=work.sample 
  dbms=xlsx
  replace
;
  sheet="%superq(sheet&i)";
  getnames=yes;
run;

Bruno

rajupottala
Calcite | Level 5

data ab;
input name$ @@;
datalines;
a b c d e f g h i j k
run;
/* above-mentioned observations are sheet names
we can link this to our sheet name and dataset name*/


data _null_;
set ab nobs=total;
call symputx("sheet"||left(_n_),name);
call symputx("cnt",total);
run;
%put &sheet2;/* check it's resovling or not in log*/
%put &cnt; /* check it's resovling or not in log*/

%macro import();
%do i=1 %to &cnt;
proc import datafile="G:\SAS Practice\Result\raju.xlsx"
out=&&sheet&i
dbms=xlsx
replace;
sheet="&&sheet&i";
run;
%end;
%mend import;
%import();

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 5672 views
  • 1 like
  • 5 in conversation