topic Re: Problem use libname with excel file in SAS Procedures

Problem use libname with excel file

Mike_Davis — Mon, 13 Aug 2012 17:47:51 GMT

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

Re: Problem use libname with excel file

Linlin — Mon, 13 Aug 2012 18:09:30 GMT

try:

data sheetone;

set xl."sheetone$"n;

run;

Re: Problem use libname with excel file

Haikuo — Mon, 13 Aug 2012 18:10:07 GMT

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

Haikuo

Re: Problem use libname with excel file

Mike_Davis — Mon, 13 Aug 2012 20:36:30 GMT

Thanks!

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

Thanks

Mike

Re: Problem use libname with excel file

Haikuo — Mon, 13 Aug 2012 20:55:08 GMT

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

Re: Problem use libname with excel file

Peter_C — Mon, 13 Aug 2012 21:50:16 GMT

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

Re: Problem use libname with excel file

Ksharp — Tue, 14 Aug 2012 04:18:43 GMT

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

Re: Problem use libname with excel file

Mike_Davis — Tue, 14 Aug 2012 21:56:31 GMT

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

Re: Problem use libname with excel file

art297 — Tue, 14 Aug 2012 22:21:36 GMT

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 ;

Re: Problem use libname with excel file

Peter_C — Wed, 15 Aug 2012 06:10:04 GMT

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

Re: Problem use libname with excel file

Peter_C — Wed, 15 Aug 2012 06:18:30 GMT

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 ;

Re: Problem use libname with excel file

art297 — Wed, 15 Aug 2012 13:03:34 GMT

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;