BookmarkSubscribeRSS Feed
Mike_Davis
Fluorite | Level 6

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

11 REPLIES 11
Linlin
Lapis Lazuli | Level 10

try:

data sheetone;

set xl."sheetone$"n;

run;

Mike_Davis
Fluorite | Level 6

Thanks!

Is there any help information I coud find for this solution? what does the "n" means?

Thanks

Mike

Haikuo
Onyx | Level 15

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

Peter_C
Rhodochrosite | Level 12

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

Ksharp
Super User

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

Peter_C
Rhodochrosite | Level 12

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 ;

Mike_Davis
Fluorite | Level 6


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

art297
Opal | Level 21

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 ;

Peter_C
Rhodochrosite | Level 12

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

art297
Opal | Level 21

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;

Haikuo
Onyx | Level 15

Instead of 'set xl.sheetone$;', try this: set xl.'sheetone$'n;

Haikuo

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1355 views
  • 7 likes
  • 6 in conversation