Help using Base SAS procedures

Problem use libname with excel file

Reply
Regular Contributor
Posts: 222

Problem use libname with excel file

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

Super Contributor
Posts: 1,636

Re: Problem use libname with excel file

Posted in reply to Mike_Davis

try:

data sheetone;

set xl."sheetone$"n;

run;

Regular Contributor
Posts: 222

Re: Problem use libname with excel file

Thanks!

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

Thanks

Mike

Respected Advisor
Posts: 3,156

Re: Problem use libname with excel file

Posted in reply to Mike_Davis

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

Valued Guide
Posts: 2,177

Re: Problem use libname with excel file

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

Super User
Posts: 10,020

Re: Problem use libname with excel file

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

Valued Guide
Posts: 2,177

Re: Problem use libname with excel file

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 ;

Regular Contributor
Posts: 222

Re: Problem use libname with excel file


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

PROC Star
Posts: 7,468

Re: Problem use libname with excel file

Posted in reply to Mike_Davis

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 ;

Valued Guide
Posts: 2,177

Re: Problem use libname with excel file

Posted in reply to Mike_Davis

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

PROC Star
Posts: 7,468

Re: Problem use libname with excel file

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;

Respected Advisor
Posts: 3,156

Re: Problem use libname with excel file

Posted in reply to Mike_Davis

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

Haikuo

Ask a Question
Discussion stats
  • 11 replies
  • 367 views
  • 7 likes
  • 6 in conversation