Mary,

I have had very few problems with a large number of spreadsheet imports using EG 4.1, HotFix 6. I also have had great luck using this to import e-mail for analysis with the ability to import from and Exchange server in EG. Finally, OLE DB and ODBC also have been really nice direct from the EG client...

Importing Excel from DMS or from Unix via Access to PC File Formats is a poor experience indeed...

Regards, Stephen

On Wed, Jun 18, 2008 at 9:53 AM, Mary <mlhoward@avalon.net> wrote:

> This reinforces my larger point- **I** can barely do this with 20 years of > SAS experience, let alone someone just learning SAS! So what happens is > that the new user decides that SAS is just too complicated and goes over to > learn R or something else. And SAS loses one more potential lifelong user > just because it hasn't bothered to write a decent import routine from Excel. > > In Excel, the import procedure allows you to change the type of any field > before finishing the import; you TELL IT what data type you want, not have > it guess at all! One example of SAS becoming more compatible with Excel is > to have its interactive File--Import steps work the same way (with the added > advantage of getting to save the SAS code so one doesn't have to do it > again). > > It is these kind of things that SAS needs to work on, in my opinion. Yes, > someone mentioned that SAS was mainly a mainframe package until the PC > version in V5, and the problem is that it still feels like a mainframe > package (like TSO). Perhaps it should just leave the mainframe package > alone at this point and focus only on developing a good windows package. > The ability to read and write from Excel WITHOUT jumping through major hoops > would be a good start towards making SAS viable in the future. > > -Mary > ----- Original Message ----- > From: data _null_, > To: SAS-L@LISTSERV.UGA.EDU > Sent: Tuesday, June 17, 2008 9:49 PM > Subject: Re: SAS's Future > > > You could set guessing rows in your SAS program using a program > similar to the following. If you were so inclined you could save the > current value and reset it after using a SAS interface to the MS Jet > Engine. For the macro crowd this could be packaged into a macro etc. > etc. > > filename ft23f001 "&workpath.\GuessRows.vbs" recfm=v lrecl=512; > data _null_; > file ft23f001; > guessrows = 2**14; > put 'Set objShell = WScript.CreateObject("WScript.Shell")'; > put 'const target = > "HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows"'; > put 'With objShell'; > put ' GuessRows = .RegRead (target)'; > put ' WScript.Echo "Current Value of GuessRows=" & GuessRows'; > put ' .RegWrite target,' guessRows ', "REG_DWORD"'; > put ' GuessRows = .RegRead (target)'; > put ' WScript.Echo "GuessRows Set to GuessRows=" & GuessRows'; > put ' End with'; > run; > > data _null_; > length command path $256; > path = pathname('ft23f001'); > command = catx(' ','cscript',quote(trim(path))); > infile dummy pipe filevar=command lrecl=1024; > input; > list; > run; > > > > On 6/17/08, Mike Zdeb <msz03@albany.edu> wrote: > > hi ... re:import all spreadsheets ... it's not automatic, but here's an > idea ... not PROC IMPORT, > > but the LIBNAME engine ... GUESSINGROWS is not allowed, but if you look > at ... > > > > http://www2.sas.com/proceedings/sugi31/020-31.pdf > > > > you can see how to change the default GUESSINGROWS to a very large value > via a Windows registry > > tweak (thanks Ed Heaton !!!) > > > > > > * > > one XLS file ... same variables in each sheet ... make one data set; > > could be different variables, but all the normal rules apply as to types > and lenghts > > as with concatenating data sets > > ; > > > > libname x 'z:\test.xls'; > > > > proc sql noprint; > > select catt('x."',memname,'"n') into :sheets separated by ' ' > > from dictionary.tables where libname='X'; > > quit; > > > > data all_the_same; > > set &sheets; > > run; > > > > libname x clear; > > > > * one XLS file ... different variables in each sheet ... make one data > set per sheet; > > > > libname x 'z:\test.xls'; > > > > proc sql noprint; > > create table sheets as > > select catt('x."',memname,'"n') as sheet > > from dictionary.tables where libname='X'; > > quit; > > > > data _null_; > > set sheets; > > * new data set name same as sheet name; > > * could add COMPRESS function to get rid of not-allowed characters (e.g. > spaces); > > new = scan(sheet,2,'"$'); > > call execute(catx(' ', > > > > 'data',new,';', > > 'set',sheet,';', > > 'run;' > > > > )); > > run; > > > > libname x clear; > > > > > > -- > > Mike Zdeb > > U@Albany School of Public Health > > One University Place > > Rensselaer, New York 12144-3456 > > P/518-402-6479 F/630-604-1475 > > > > > Actually, I think SAS has more basic things it should be doing; its > interface to Microsoft Excel > > > isn't very good, in that it doesn't allow specification of variable > type and lets its import guess > > > based on the first 5 rows. Also it is not currently supporting > Office 2007, which it needs to be > > > doing. The ability to import all spreadsheets within a workbook at > once would also be a great > > > help. > > > > > >