ODS Markup ExcelXP
in [SAS]
|
From: Gerhard Hellriegel on 6 Dec 2006 12:32 You say that "it's all garbage". I think it should be HTML, means readable html code. I think, that attachment is converted from EBCDIC to ASCII. Do the following to verify that: store the output to a host dataset and have a look with the editor or browse. Is it HTML? Or something like that? If it is ok, have a closer look to the PC file: does it also look as a html file? Or close to it? Means that perhaps some html triggers are wrong. If it's kind of readable on PC (rename the extension to TXT and use the windows editor or a hex-editor), are there some curious chars? What about @, [, ], <, >, ... Do they look ok? If you really see garbage and nothing redable: try to download the host-file via filetransfer - try binary and EBCDIC - what are the results? Try to provide a few more details and experiment a bit to get closer to the problem. Regards, Gerhard On Wed, 6 Dec 2006 11:59:27 -0500, McWhorter, Keith <Kmcwhort(a)GTA.GA.GOV> wrote: >Hi, > > > >I'm using an example from the paper "SAS Excels!" to create output as >XLS to open in Excel. However, I'm running this on the mainframe and >emailing it to myself. When I open the file it's all garbage. I've used >the email part of the code with PDFs successfully. Not sure about all >the parameters I may need for XLS though. > >Anyone see anything glaringly wrong? > > > >Thanks! > > > >Environment: z/OS 1.4, SAS 9.1.3 SP4 > > > >Code: > > > >ods listing close; > >ods markup tagset=excelxp body='SGSS.SAS.HTML(DHRT)' ; > > > >PROC PRINT data=dhrtb split='*' noobs; > > VAR job jesnr sdate stime exectm cputm edate etime; > > FORMAT sdate edate MMDDYY10. > > stime etime TIME8. > > ; > > Label sdate = 'Start*Date' > > stime = 'Start*Time' > > edate = 'End*Date' > > etime = 'End*Time' > > job = 'Job' > > ; > >run; > >ods markup close; > > > >filename tempmail email > > FROM=("SAS(a)gta.ga.gov") > > TO=("kmcwhort(a)gta.ga.gov") > > subject="DHR Jobs" > > type="text/plain" > > attach=("SGSS.SAS.HTML(DHRT)" > > content_type="application/xls" extension='xls'); > > > >DATA _NULL_; > > FILE tempmail; > > put 'As requested, attached is a listing of'; > > put '@DM Jobs Run on SYSTB November 13 - 30'; > >run; > >/* > > > >Keith McWhorter > >Software Systems Technical Lead > >Georgia Technology Authority > >404-656-9068 > > > >Take the attitude of a student: never be too big to ask questions, never >know too much to learn something new. > > --- Og Mandino --- > > > > > >
From: Keith McWhorter on 6 Dec 2006 13:10 Gerhard, In the Excel spreadsheet it was truly garbage. However, I took your advice and looked at the data created on the mainframe and it is readable xml/html. I file transferred it to the PC as ASCII and it is readable in Notepad. However, attempting to open in Excel causes a pop up window called "Problems during Load" "problems came up in the following areas during load:" and in the window below it lists "Style". At least I'm a little closer! Thanks, keith
From: John Birken on 6 Dec 2006 15:06 Keith: 1. Check your ASCII in a true programming editor. Notepad doesn't show all text problems. I expect data from my mainframe to have extra characters - not the garbage you're describing though. 2. List/send me 2 lines of the ASCII you're trying to import. You can use ^ for garbage characters (if need be -otherwise it may not be transmitted) and send me the few key stroke you use to get into excel. There may be simple solutions to your problem. HTwillH, John jbirken(a)cdc.gov -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Keith McWhorter Sent: Wednesday, December 06, 2006 1:11 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: ODS Markup ExcelXP Gerhard, In the Excel spreadsheet it was truly garbage. However, I took your advice and looked at the data created on the mainframe and it is readable xml/html. I file transferred it to the PC as ASCII and it is readable in Notepad. However, attempting to open in Excel causes a pop up window called "Problems during Load" "problems came up in the following areas during load:" and in the window below it lists "Style". At least I'm a little closer! Thanks, keith
From: yzg9 on 7 Dec 2006 13:41 Uh-oh. I've seen similar, very difficult to determine pop-up messages from Excel after using the ExcelXP tagset. What I found was that Excel wasn't happy with the XML code generated from the tagset if the style you're using isn't one of the given styles. If I kept the style as Journal, Printer, Minimal, or Statistical, Excel would load the resulting XML file. When I had modified one of the styles, I ended up having mixed results, spending more time than I wanted. I decided to make the style modifications in the Proc Print code instead of at the page style level. Haven't applied this to Proc Report yet. John Gerstle, MS Biostatistician Northrop Grumman CDC Information Technological Support Contract (CITS) NCHSTP \DHAP \HICSB \Research, Analysis, and Evaluation Section Centers for Disease Control and Prevention "Boss. We've got cats." "Meow" "All truth passes through three stages: First, it is ridiculed; Second, it is violently opposed; Third, it is accepted as being self-evident." - Arthur Schopenhauer (1830) >>-----Original Message----- >>From: owner-sas-l(a)listserv.uga.edu [mailto:owner-sas-l(a)listserv.uga.edu] >>On Behalf Of Keith McWhorter >>Sent: Wednesday, December 06, 2006 1:11 PM >>To: SAS-L(a)LISTSERV.UGA.EDU >>Subject: Re: ODS Markup ExcelXP >> >>Gerhard, >> >>In the Excel spreadsheet it was truly garbage. However, I took your advice >>and looked at the data created on the mainframe and it is readable >>xml/html. >>I file transferred it to the PC as ASCII and it is readable in Notepad. >>However, attempting to open in Excel causes a pop up window called >>"Problems >>during Load" "problems came up in the following areas during load:" and >>in >>the window below it lists "Style". >> >>At least I'm a little closer! >> >>Thanks, >>keith
From: Jack Hamilton on 7 Dec 2006 18:08
This works for me: ===== //EMAILXLS JOB //* /*ROUTE PRINT FETCH //* //S1REPORT EXEC SASPROD, // OPTIONS='noovp nocenter errorabend errorcheck=strict' //XLSFILE DD DSN=&SYSUID..REPORT.XLS,DISP=(MOD,CATLG,DELETE), // LRECL=8196,RECFM=VB,SPACE=(8196,(1000,2000)), // UNIT=SYSWRK //SYSIN DD *,DLM='!!' /* Get latest copy of tagset from SAS web site */ filename tagset http "%lowcase( HTTP://SUPPORT.SAS.COM:80/RND/BASE/TOPICS/ODSMARKUP/EXCLTAGS.TPL )"; %include tagset / nosource2; filename tagset clear; /* Empty the output file, in case this is a rerun. */ data _null_; file xlsfile old; run; /* Tell SAS where to send output */ ods tagsets.excelxp file=xlsfile record_separator=none style=sansprinter; ods listing close; /* Print the first sheet */ ods tagsets.excelxp options(sheet_name='Raw Data' frozen_headers='1' row_repeat='1' autofilter='1-5' ); title 'Raw Data'; proc print data=sashelp.prdsale noobs; var country region division prodtype product year quarter month actual predict; run; /* Print the second sheet, triggered by a new ODS statement */ /* to the ExcelXP tagset without a new FILE=. */ ods tagsets.excelxp options(sheet_name='Sales Summary' autofilter='3' ); proc report data=sashelp.prdsale missing nowindows nocenter; column country region prodtype actual predict; define country / group 'Country'; define region / group 'Region'; define prodtype / group 'Product Type'; define actual / sum 'Actual Sales'; define predict / sum 'Predicted Sales'; run; ods _all_ close; ods listing; /* Close and free the output file. */ data _null_; length dsn $44.; file xlsfile mod close=free filename=dsn; call symput('REPORTDSN', trim(dsn)); run; filename email email attach=("&REPORTDSN." name='EmailXLS' extension='xls' lrecl=8196) to='jfh(a)alumni.stanford.org' from='Jack.Hamilton(a)kp.org' subject='Excel test'; data _null_; file email; put 'Results are attached.'; run; filename email clear; !! ===== record_separator=none is important on MVS. On Wed, 6 Dec 2006 11:59:27 -0500, "McWhorter, Keith" <Kmcwhort(a)GTA.GA.GOV> said: > Hi, > > > > I'm using an example from the paper "SAS Excels!" to create output as > XLS to open in Excel. However, I'm running this on the mainframe and > emailing it to myself. When I open the file it's all garbage. I've used > the email part of the code with PDFs successfully. Not sure about all > the parameters I may need for XLS though. > > Anyone see anything glaringly wrong? > > > > Thanks! > > > > Environment: z/OS 1.4, SAS 9.1.3 SP4 > > > > Code: > > > > ods listing close; > > ods markup tagset=excelxp body='SGSS.SAS.HTML(DHRT)' ; > > > > PROC PRINT data=dhrtb split='*' noobs; > > VAR job jesnr sdate stime exectm cputm edate etime; > > FORMAT sdate edate MMDDYY10. > > stime etime TIME8. > > ; > > Label sdate = 'Start*Date' > > stime = 'Start*Time' > > edate = 'End*Date' > > etime = 'End*Time' > > job = 'Job' > > ; > > run; > > ods markup close; > > > > filename tempmail email > > FROM=("SAS(a)gta.ga.gov") > > TO=("kmcwhort(a)gta.ga.gov") > > subject="DHR Jobs" > > type="text/plain" > > attach=("SGSS.SAS.HTML(DHRT)" > > content_type="application/xls" extension='xls'); > > > > DATA _NULL_; > > FILE tempmail; > > put 'As requested, attached is a listing of'; > > put '@DM Jobs Run on SYSTB November 13 - 30'; > > run; > > /* > > > > Keith McWhorter > > Software Systems Technical Lead > > Georgia Technology Authority > > 404-656-9068 > > > > Take the attitude of a student: never be too big to ask questions, never > know too much to learn something new. > > --- Og Mandino --- > > > > > > -- Jack Hamilton Sacramento, California jfh(a)alumni.stanford.org |