comp.soft-sys.sas - The SAS statistics package.
Hi All, I'm working with Vincent DelGobbo's XML tagset after reading many good things about it. When I setup my macro variable SAMPDIR at the top of CompleteCode.sas, and I run the code in Enterprise Guide, I get an error that says: ERROR: Unable to assign a FILEREF for the Excel XML file I've included the relevant log section below. Please note that the reference for SAMPDIR is a UNC style network path, and the folder exists, as the program successfully includes the ExcelXP.sas program and the LoadXL.sas program. If anybody has any suggestions for how to resolve this issue, that would be excellent. Thanks, Ben 31 *; 32 * Include recent version of the ExcelXP ODS tagset. 33 *; 34 35 %inc "&SAMPDIR.ExcelXP.sas"; 5606 5607 *; 5608 * Include the macro (XLXP2SAS) to import Excel XML to SAS tables. 5609 *; 5610 5611 %inc "&SAMPDIR.LoadXL.sas"; 6143 6144 filename myxml "&SAMPDIR.DataToImport.xml"; 6145 filename mymap "&SAMPDIR.ExcelXP.map"; 6146 6147 %xlxp2sas( excelfile=FILEREF:myxml, 6148 mapfile=FILEREF:mymap) ERROR: Unable to assign a FILEREF for the Excel XML file.
Hi, I am pretty new to the excelxp taget world, but am finding my way. My current prob is I am using a proc print, the member_var is a character, but only has numerics. So when ods outputs it to xlm the data type is numeric instead of string. I need it to be string so if someone saves the excel document I dont lose the leading zeros of the member_var. How can I change the data type of a variable when it writes it to xml. My code is below. ods tagsets.ExcelXP path="xxx" file="test.xls"); proc print data = test_dset noobs; var member_var update; run; ods tagsets.ExcelXP close;
Hi Everyone, I've been creating excel reports from sas for a while now, and still struggle to do all the unique 'one time' things that each report invariably requires. I do it sporadically enough where I find myself re-learning it all each time. Does anybody know of a good book that really covers the full depth of creating excel spreadsheets and all the tagset modifications and such that are done? At this point somebody has had to have written a book on it! My current reference section for this involves a whole pile of printouts from various papers, websites, etc. Someone somewhere has had to put it all together. Thanks! --Bob
Hi Perry, I don't want you to have to take a 'long' time, so I'll tell you my secret for finding these in approx 30 seconds... For example, your first question was that the freeze panes wasn't working. Since XML is merely text files then your favorite editor works just fine, PLUS anything that will compare two text files. I use UltraEdit which has lots of features including an nice compare feature, or any DIFF or line compare utility will do. I open Excel, type in 'Fred" in A1, type in 'Barney' in A2, and make sure the focus is left in A2(where I am going to eventually have the freeze pane axis). Here is trick number 1, SAVE the spreadsheet as XML to disk at this point *without* the freeze panes. Then trick number 2 is to add the freeze panes and save to disk as XML to another filename. Your compare utility will show you the exact lines(syntax) of *that* feature, as a difference between the two text files. Sometimes a larger sample requires Wilma, Betty, Pebbles, Dino and BammBamm. Occasionally Mr Slate. So that is the quick way to find out how to add any new Excel feature or cosmetic. Just compare the XML text files of before and after adding just that one change only! In your case, your syntax for freeze panes was perfectly fine. So I did a copy and paste of your 4 or 5 option groups one at a time into my sample XML text file and then opened the workbook to see if the syntax worked okay. That is how I found the <:Header. Since I typically just use Excel to make my original and then wrap PUT statements around it, and then use Excel to make single changes and locate/copy/paste with PUT statements just the change lines, I have the luxury of rarely having extraneous typos. Since I many times only take a few minutes away from work to do SAS-L responses, I did you wrong the first go-'round by not pondering that your <WorksheetOptions header may have been hand-typed, cuz I always just copy and paste it. Therefore I had tested your original set of options in a valid header string and they all worked. It wasn't until you mentioned later that things still were not working that I copy and pasted ALL of your code into my small sample that with the compare utility again immediately spotted the header typo. With those easy/cheap tools(Excel&UltraEdit$39) plus the approach above, I found all three of your answers accumulatively in a total probably less than 90 seconds. So, Happy Fast Resolving, Mark -----Original Message----- From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of XXXX@XXXXX.COM Sent: Thursday, August 09, 2007 7:33 AM To: XXXX@XXXXX.COM Subject: Re: Excel Spreadsheet XML WE HAVE SUCCESS!!! Mark, Thank you SO much. I don't want to think about how long it would have taken me to find that typo without your help. On Aug 8, 12:04 pm, XXXX@XXXXX.COM ("Terjeson, Mark") wrote: > Hi Perry, > > I think you'll have good luck with your entire > macro if you change one hyphen to a colon. > > Change > put ' <WorksheetOptions > xmlns="urn-schemas-microsoft-com:office:excel">'; > To > put ' <WorksheetOptions > xmlns="urn:schemas-microsoft-com:office:excel">'; > > See if things don't come alive now! > > Let us know how it goes..... > > Hope this is helpful. > > Mark Terjeson > Senior Programmer Analyst, IM&R > Russell Investments > > Russell Investments > Global Leaders in Multi-Manager Investing