BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Have just answered this exact query here:

Ravikumarkummari
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Ravikumarkummari
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

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

Tom
Super User Tom
Super User

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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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