Libname Excel

Reply
Regular Contributor
Posts: 181

Libname Excel

Hi SAS Gurus,

I have an excel file containing multiple sheets. I tried to import it into SAS using LIBNAME excel engine. I know it doesn't allow me to open the excel file. I thought to copy the library into another library. And then clear the library using LIBNAME CLEAR option.

Below the code that i am using :

LIBNAME myxl excel 'C:\Documents and Settings\Desktop\SAS Sample Files/All Excel Sheets.xls' ;                               

                                                      

LIBNAME dest  'C:\Documents and Settings\Desktop\SAS Sample Files';                                                                                                                                            

proc datasets library=myxl;                                                                                                            

   copy out=dest;                                                                                                                      

run;                                                                                                                                   

                                                                                                                                       

Quit;                                                                                                                                  

                                                                                                                                       

LIBNAME myxl CLEAR; 

ERROR: The value 'SHEET1$'n is not a valid SAS name.

ERROR: File DEST.'Sheet1$'n.DATA has not been saved because copy could not be completed.

ERROR: The value 'SHEET2$'n is not a valid SAS namRROR: File DEST.'Sheet2$'n.DATA has not been saved because copy could not be completed.

Super Contributor
Posts: 297

Re: Libname Excel

Hi Ujjawal,

In SAS 9.1 you can use the option VALIDVARNAME=ANY to achieve this, however I have been unable to validate this on my 9.3 installation.

If you are using SAS 9.2 on wards adding OPTIONS VALIDMEMNAME= EXTEND; to the start of your code will allow you to use these names in a Proc Copy, which I believe is more efficient than a Proc Datasets.

For more information check out SAS(R) 9.2 Language Reference: Concepts, Second Edition.

Regards,

Scott

Trusted Advisor
Posts: 1,131

Re: Libname Excel

Hi Ujjawal,

I think you need to use proc import procedure to get the data from excel to sas7bdat format.

here is the syntax for the same

filename file 'C:\Documents and Settings\Desktop\SAS Sample Files/All Excel Sheets.xls'

proc import datafile=file out=dataset dbms=excel replace;

sheet='sheet$';

run;

in the sheet statement you can mentioned the sheet name which you want to convert into the sas dataset.

Hope this is helpful.

Thanks,

Jagadish

Thanks,
Jag
Super User
Posts: 17,946

Re: Libname Excel

You should be able to both see the excel file and import all the data from the libname statement (assuming windows).

After running

LIBNAME myxl excel 'C:\Documents and Settings\Desktop\SAS Sample Files/All Excel Sheets.xls' ;  


Go to the library viewer and you'll see the myxl library with a little world icon on it. Open that and you should see the sheets. Then you should be able to open the sheets and see the data.


You can import it as follows:


data input;

set myxl.'Sheet1$'n;

run;


Then close the connection:


library myxl;


Here's some code that will import all the sheets in an excel file:

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1211e&L=sas-l&m=33249&P=8001

Libname in 'C:\Park\bhupindertest.xlsx';


Proc contents data = in._all_ out = a noprint;;
run;


Data _null_ ;
set a (keep = memname) end = eof;;
name = compress(memname, '$');
name = translate(trim(name) , '_', ' ');
str =compbl( "Data " || name || "; set in.'" || memname ||"'n;" );


Call Execute (str);
if eof then do;
str = 'run;';
call execute (str);

Regular Contributor
Posts: 181

Re: Libname Excel

Thanks everyone for your help !!

@ Reeza : Thanks a ton for sharing the link. I have some different code to import multiple sheets into SAS. And that serves the purpose very well. However, i would like to know the flexibility of LIBNAME EXCEL . Can't we copy the whole library containing excel sheets data sets into another library using PROC DATASET or COPY? I want this to be automated because there could be 50-60 sheets. And it's a very time consuming approach to read the name and run code for it.


Any help would be highly appreciated !!

Super User
Posts: 17,946

Re: Libname Excel

Have you tried the second set of code? That should import all the sheets, as long as the names are SAS compliant.

Super Contributor
Posts: 297

Re: Libname Excel

Using the following PROC COPY will do the trick and maintains the dataset naming convention used in the original XLS woorkbook.

OPTIONS VALIDMEMNAME= EXTEND;

LIBNAME XLSFILE PCFILES TYPE=EXCEL PORT=8621 SERVER=LOCALHOST PATH = "E:\TEST50.XLS";

LIBNAME OUTDIR "E:\TEMP";

PROC COPY IN= XLSFILE OUT=OUTDIR;

RUN;

Ask a Question
Discussion stats
  • 6 replies
  • 764 views
  • 1 like
  • 4 in conversation