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.
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 ;
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.
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).
Through proc import or Data step infile statement it is possible to give excel sheet names as dataset names
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.
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
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 ;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.