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....
Thanks
Hi,
Use:
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.
data sheet1;
set my_xls.'sheet1$';
run;
Reference: http://www2.sas.com/proceedings/sugi31/024-31.pdf
Hi ,
I am not able to assign library name with above format.......it says invalid format
Missing the engine statement - excel, please see my previous post.
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.
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
Perhaps it is the platform
@rakeshvv
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
I am not running it on SAS Enterprise guide but was running on sas unix platform
Check your SAS licence - prod setinit noalias; run; If you dont have SAS/ACCESS Interface to PC Files listed then this will explain your errors.
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.
Good luck
whatever worksheet tab names are present, SAS can use them using the style I refer to as "name constant.
I.E. 'tabname'n
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 ;
run ;
of course results depend on the qualitty of the data in excel .
peterC
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.