04-09-2014 08:21 AM
I have a 2010 excel with N number of tab sheets..i would like to create a program which
would create N number of datasets with tab sheets name as name of the dataset....if tab sheets
have numerical number as name....sas should assign default name to the dataset otherwise name of the
tabsheet as dataset name....
04-09-2014 08:29 AM
libname my_xls excel "S:\Temp\Rob\tmp.xlsx" mixed=yes;
This will show you all the worksheets within your XLS file, it should be a simple matter to loop over them and create datasets. E.g.
04-09-2014 10:05 AM
I Used your previous code but i was getting following error.... I am using 9.2 version of sas
libname my_xls excel "/xxxxxxxxxxxxxx/abc.xlsx" mixed=yes;
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.
04-09-2014 10:11 AM
Strange, do you have SAS/ACCESS component?
It should be available in SAS 9.2 if you have SAS/ACCESS: SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition
04-09-2014 10:34 AM
Perhaps it is the platform
are you running sas on a windows platform, or elsewhere?
Are you running this code in SAS Enterprise Guide connected to a server that is not running on windows?
Then there might be a problem
04-09-2014 10:15 AM
Well, it would work if you licence sas access to pc files
but since you don't, it doesn't.
One workaround involves saving all your data sheets into csv files and loading those
Another involves DDE but in more recent machine systems that has become less reliable for more than small volumes.
04-09-2014 09:23 AM
whatever worksheet tab names are present, SAS can use them using the style I refer to as "name constant.
So If the tabs are just numbers 1,2,3,4 then a data step could bring them all in with code like
libname yours 'your excel workbook path\name' mixed= yes access= readonly ;
data all_together ;
set yours.'1'n yours.'2.'n yours.'3'n yours.'4'n ;
of course results depend on the qualitty of the data in excel .