Hello everyone,
I have a excel file named all.xls, in the excel file there are three sheets named sheetone,sheettwo and sheetthree
When after I use libname engine: libname xl "c:\temp\all.xls";
I can treat each individual sheet as SAS table.
But the problem is the Sheet's name will be changed with an additional '$' symbol,,,,,sucha as sheetone will be changed to sheetone$.
and I can open to preview the dataset but can't create other datast use this,such as if I use :
data sheetone;
set xl.sheetone$;/*I also try set xl.sheetone,still not work*/
run;
this will cause error:
ERROR: File XL.sheetone$.DATA does not exist.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, ;, END,
KEY, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.
so how I cloud make use this dataset?
Thanks
Mike
try:
data sheetone;
set xl."sheetone$"n;
run;
Thanks!
Is there any help information I coud find for this solution? what does the "n" means?
Thanks
Mike
Hi,
'$' is not allow in SAS naming convention, along with space, ',' and many other special symbols. if you run into names like that , a SAS literal has to be used. A simple way to convert it is to use nliteral(), which is also a way to make sure it complies with naming convention:
data _null_;
a='sheet1$';
a1='sheet';
b1=nliteral(a1);
b=nliteral(a);
put b= b1;
run;
Haikuo
use Hai.huo's technique to copy all sheets like
libname xl 'your.xls' ;
proc sql noprint ;
create table xnames as
select memname, nLiteral( memname ) as newName
from dictionary.members
where libname = 'XL' and memname ? '$'
;
quit ;
data _null_ ;
set xnames ;
call execute( 'proc append base=work.' !! newName );
call execute( ' data= xl.' !! quote(trim(memname)) !! 'n ; run;' );
run ;
using proc append is a convenient way of copying a data set, but beware of re-running within the same sas session or you might double up the rows of your data.
peterC
Why not put it all into a macro variable ?
proc sql noprint ;
select cats('XL.',nLiteral( memname ) ) into : list separated by ' '
from dictionary.members
where libname = 'XL'
;
quit ;
data want ;
set &list ;
run;
Ksharp
Ksharp
I was trying to generate for each tabname$, the syntax
proc append data= xl."tabname$"n
base= work.%sysfunc(nliteral( tabname$ )) ; run;
of course, wrapped in call execute() the nliteral() function appears without %sysfunc()
ArtC's suggestion is equally valid
data work.%sysfunc(nliteral( tabname$ )) ;
set xl."tabname$"n ;
run ;
Thank you ,
but errors happened when run the code
data _null_ ;
set xnames ;
call execute( 'proc append base=work.' !! newName );
call execute( ' data= xl.' !! quote(trim(memname)) !! 'n ; run;' );
run ;
ERROR: The value 'SHEET1$'n is not a valid SAS name
Thanks
Mike: I haven't followed this thread. Are you just trying to append all of the sheets into one sas file? If so, the error you are getting is because the code is trying to do something else. Does the minor change, below, do what you wanted?
libname xl 'c:\have.xls' ;
proc sql noprint ;
create table xnames as
select memname, nLiteral( memname ) as newName
from dictionary.members
where libname = 'XL' and memname ? '$'
;
quit ;
data _null_ ;
set xnames ;
call execute( 'proc append base=work.want' ); /*<- the one change */
call execute( ' data= xl.' !! quote(trim(memname)) !! 'n ; run;' );
run ;
Mike
maybe you have to add the system option
option validvarname= any ;
and I would recommend resetting that after the collection has completed, to
option validvarname= v8 ;
Peter
Message was edited by: Peter Crawford and if that does not work, please post a section of the log showing the generated code
Mike: or, if you actually want separate files created in the work directory, couldn't you just compress out the offending characters. e.g.:
libname xl 'c:\have.xls' ;
proc sql noprint ;
create table xnames as
select memname, nLiteral( memname ) as newName
from dictionary.members
where libname = 'XL' and memname ? '$'
;
quit ;
data _null_ ;
set xnames ;
call execute( 'proc append base=work.'||compress(memname,"$ " )); /*<- the one change */
call execute( ' data= xl.' !! quote(trim(memname)) !! 'n ; run;' );
run ;
libname xl clear;
Instead of 'set xl.sheetone$;', try this: set xl.'sheetone$'n;
Haikuo
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.