topic Re: how to give excel sheet names as dataset names in SAS Procedures

how to give excel sheet names as dataset names

Ravikumarkummari — Fri, 13 Mar 2015 09:40:14 GMT

Good day All.

I have an excel file. It contains 12 sheets. From Jan-Dec.

Now i import this data into sas by using proc import.

But every time i need to giving sheet name as dataset name.

Is there any possible to while importing giving sheet name as dataset name for all 12 sheets.

Thanks in advance.

Re: how to give excel sheet names as dataset names

RW9 — Fri, 13 Mar 2015 10:01:09 GMT

Hi,

Have just answered this exact query here:

Re: how to give excel sheet names as dataset names

Ravikumarkummari — Fri, 13 Mar 2015 10:07:44 GMT

I had seen your answer.

Using libname it is possible. But we want to create user defined variable in this case it is not possible to import through libname.

So is there any method to import excel sheets names as dataset names.

Re: how to give excel sheet names as dataset names

RW9 — Fri, 13 Mar 2015 10:18:56 GMT

Not as far as I am aware. You need to have something generate the "metadata" of the file for you. I suppose you could do (assuming its an xlsx file and you have 9.4):

- open the .xlsx file as a compressed file using SAS(R) 9.4 Statements: Reference, Third Edition

- locate the ...\xl\workbooks.xml file in the zip

- import that into a dataset

- then use that to get sheet names.

The problem you face is Excel. It is not a database (or anything else for that matter) so does not have metadata or supporting information. When I set data transfers up I first write a document which states format, variables, types, code lists etc. and fix a proper data transfer strcuture - e.g. CSV, XML etc. That way I know before hand what data to recieve, rather than trying to guess after the event. Whilst it is still possible to guess after the event on flat data transfer files such as CSV, Excel can contain anything, in any order, with no structure or control whatsoever. This is a principal reason why I would not accept Excel in anything other than a life or death situation (and even then only with proper detailed documentation).

Re: how to give excel sheet names as dataset names

Ravikumarkummari — Fri, 13 Mar 2015 10:52:45 GMT

Through proc import or Data step infile statement it is possible to give excel sheet names as dataset names

Re: how to give excel sheet names as dataset names

RW9 — Fri, 13 Mar 2015 11:18:20 GMT

Yes, but you you or your program needs to know what are the available sheet names, that is where the metadata would be used. So in SAS I can find out that table a has 3 columns by checking the metadata tables. With Excel you don't have this.

Re: how to give excel sheet names as dataset names

Ksharp — Fri, 13 Mar 2015 12:38:00 GMT

You gotta know these excel sheet name might be some like xx%%yy$$ , these are all illegal name for SAS dataset name .

What you gonna do when you have such sheet name ?

BTW,Libname is a good way.



libname x excel 'c:\temp\x.xls';
data _null_;
 set sashelp.vmember(keep=libname memname where=(libname='X'));
 call execute(cat('data ',compress(memname, ,'ka'),';set x.',nliteral(memname),';run;' ));
run;

Xia Keshan

Message was edited by: xia keshan

Re: how to give excel sheet names as dataset names

Tom — Fri, 13 Mar 2015 13:19:34 GMT

Upgrade to SAS 9.4 (TS1M2) and use XLSX files then you can use the XLSX libname engine.

libname x xlsx 'c:\downloads\test.xlsx';

proc sql noprint;

select memname into : dslist separated by ' '

from dictionary.members

where libname='X'

;

quit;

If you have an older version of SAS but you are using XLSX files you can read the metadata out of the XLSX file to generate the list of member names.

Here is some SAS code to do that.

%let filename='c:\downloads\test.xlsx';

%let sheets=work.sheets;

*----------------------------------------------------------------------;

* Generate XMLMAP to read the sheetnames from xl/workbook.xml ;

*----------------------------------------------------------------------;

filename _wbmap temp;

data _null_;

file _wbmap;

put '<SXLEMAP version="2.1"><TABLE name="Sheets">'

/ '<TABLE-PATH>/workbook/sheets/sheet</TABLE-PATH>'

/ '<COLUMN name="Sheet"><TYPE>character</TYPE>'

/ '<DESCRIPTION>Sheet Name</DESCRIPTION>'

/ '<PATH>/workbook/sheets/sheet/@name</PATH>'

/ '<DATATYPE>string</DATATYPE><LENGTH>32</LENGTH>'

/ '</COLUMN>'

/ '<COLUMN name="State"><TYPE>character</TYPE>'

/ '<DESCRIPTION>Sheet State</DESCRIPTION>'

/ '<PATH>/workbook/sheets/sheet/@state</PATH>'

/ '<DATATYPE>string</DATATYPE><LENGTH>20</LENGTH>'

/ '</COLUMN>'

/ '</TABLE></SXLEMAP>'

;

run;

*----------------------------------------------------------------------;

* Copy xl/workbook.xml from XLSX file to physical file ;

%* Note: Cannot use ZIP filename engine with XMLV2 libname engine ;

/* filename _wb pipe %sysfunc(quote(unzip -p &filename xl/workbook.xml)); */

*----------------------------------------------------------------------;

filename _wbzip ZIP &filename member='xl/workbook.xml';

filename _wb temp ;

data _null_;

infile _wbzip lrecl=30000;

file _wb lrecl=30000;

input;

put _infile_;

run;

*----------------------------------------------------------------------;

* Generate LIBNAME pointing to copy of xl/workbook.xml from XLSX file ;

*----------------------------------------------------------------------;

libname _wb xmlv2 xmlmap=_wbmap ;

*----------------------------------------------------------------------;

* Read sheet names from XLSX file into a SAS dataset. ;

* Create valid SAS dataset name from sheetname or sheetnumber. ;

*----------------------------------------------------------------------;

filename extract temp;

data &sheets ;

if eof then call symputx('nsheets',_n_-1);

length Number 8;

set _wb.sheets end=eof;

number+1;

length Memname $32 Filename $256 ;

label number='Sheet Number' memname='Mapped SAS Memname' filename='Source Filename' ;

filename = &filename ;

if ^nvalid(compress(sheet),'v7') then memname = cats('Sheet',number);

else memname = translate(trim(compbl(sheet)),'_',' ');

run;

%let sheets=&syslast;

*----------------------------------------------------------------------;

* Clear the libname and filenames used in reading the sheetnames. ;

*----------------------------------------------------------------------;

libname _wb clear ;

filename _wb clear ;

filename _wbzip clear ;

filename _wbmap clear ;