Help using Base SAS procedures

how to give excel sheet names as dataset names

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

how to give excel sheet names as dataset names

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.


Accepted Solutions
Solution
‎03-13-2015 09:19 AM
Super User
Super User
Posts: 7,072

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

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 ;

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

Hi,

Have just answered this exact query here:

Frequent Contributor
Posts: 111

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

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.

Super User
Super User
Posts: 7,977

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

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). 

Frequent Contributor
Posts: 111

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

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

Super User
Super User
Posts: 7,977

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

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.

Super User
Posts: 10,041

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

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

Solution
‎03-13-2015 09:19 AM
Super User
Super User
Posts: 7,072

Re: how to give excel sheet names as dataset names

Posted in reply to Ravikumarkummari

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 ;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 2165 views
  • 3 likes
  • 4 in conversation