ODS delima 1

[Kwok]

Hi ,

We are trying to write out a sas dataset from MVS (v 9.1) to a PC server with the following code:

DATA TEST;
LENGTH ID $14. ;
SET SASHELP.CLASS;
N=_N_;
ID=CATS(' ','123456789122',PUT(N,Z2.)) ;
RUN;
PROC PRINT;
RUN;

FILENAME CUSTX FTP "EXCEL.XLS"
HOST=....
CD="\.......\TEMP"
USER='ANONYMOUS' PASS="&PASS"
RECFM=S;

ODS TAGSETS.EXCELXP FILE=CUSTX RS=NONE TRANTAB=ASCII STYLE=MINIMAL
OPTIONS(SHEET_NAME="THIS IS A TEST" FROZEN_HEADERS='YES');

PROC PRINT DATA=TEST NOOBS LABEL ;
VAR ID/STYLE(DATA)={TAGATTR="FORMAT:00000000000000"};
VAR _ALL_ ;
TITLE1 'THIS IS A TITLE TEST' ;
RUN;

The code works except

1. it did recogonized the Excel tagset options. No frozon_header and the sheet name is 'Table 1 - Data Set WORK.TEST' instead of the one we defined.

2. There is no title in the output spreadsheet. We tried use the HTML tagsets and it will ouput a title but it will not except the statement
VAR ID/STYLE(DATA)={TAGATTR="FORMAT:00000000000000"};
and the output variable ID will formatted 1.23457E+13 instead of 12345678912201

3. When we use VAR ID/STYLE(DATA)={TAGATTR="FORMAT:00000000000000"};
is ther any good idea to exclude the variable from the _ALL_ list.We have datasets that has over 50 variables and multi STYLE(DATA)= . It is a pain to type out the rest of the print list.

Thanks for the help we can get..

[Cynthia_sas]

Hi:
When I run your code in Windows:
1) the sheet name is correct
and the frozen headers -do- work (although, because SASHELP.CLASS is so small, only the first observation "scrolls")

The fact that you do not see a changed sheet name or frozen headers is an issue you should report and resolve through SAS Technical Support.

2) You will never see the SAS title, by default, -inside- the spreadsheet -- the SAS title goes into the header area of the spreadsheet -- and you view it with Print Preview -- this is the normal behavior (you have to change it with the EMBEDDED_TITLES suboption. For more information about ExcelXP tagset suboptions, refer to this paper:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

3) _ALL_ means _ALL_ -- the behavior you see is the normal behavior. You can't exclude ID from _ALL_. However, you could use some macro variable approaches something like: VAR &REST;

where &REST was a macro variable that contained the list of variables you wanted -- without ID in the list. You can create a list of variable names by using the DICTIONARY.COLUMNS or SASHELP.VCOLUMN tables.

One possible PROC SQL step to create such a macro variable might be:
[pre]
proc sql;
select name into :rest
from dictionary.columns
where upcase(name) ne 'ID' and
(libname = 'WORK' and memname = 'TEST');
quit;
%put the other variable names are: &rest;
[/pre]

for more information on the "DICTIONARY" tables, refer to:
http://www2.sas.com/proceedings/sugi26/p017-26.pdf
http://www.bassettconsulting.com/dict_slides.pdf
http://analytics.ncsu.edu/sesug/2005/TU02_05.PDF
http://www.mwsug.org/proceedings/2009/tutorials/MWSUG-2009-T08.pdf

cynthia

[Kwok]

Thanks

[sbb]

I don't believe that the TAGSET EXCELXP is supported on SAS z/OS (MVS), which is where I understand SAS is executing.

Scott Barry
SBBWorks, Inc.

[Kwok]

Hi Scott,

We can write out spreadsheet with tagsets.excelxp from MVS.

[Peter_C]

although tagsets.excelxp is supported on z/OS, some sites might have to update the tagset to get the expected behaviour.
(our zOS SAS913 was operating the v1.37 of the tagsets.excelxp and that earlier version is much improved at later versions)

[Cynthia_sas]

I double-checked with one of the ODS developers and here's the response:
<beginquote>
"Yes it (TAGSETS.EXCELXP) does work on MVS.

Running tagsets on MVS is sometimes tricky because they frequently have settings (on MVS) that conflict with tagset code line lengths.

It looks to me like they need to update their tagset. They probably have an older version that doesn't support frozen headers. It may not even support the format via TAGATTR.

Tech Support usually keeps a compiled version of the latest tagsets for update purposes... So if (the person) contacts Tech Support, then TS can send them the itemstore directly." <endquote>

So, it sounds like the original poster might need an updated ExcelXP tagset and work with Tech Support to get it.

cynthia

[JackHamilton]

I use an LRECL of 2048 and haven't had problems.

The important thing is to add

record_separator=none

to the ODS tagsets.excelxp statement. Otherwise, you will get mysterious failures.

[Kwok]

Thanks for all your reponse. We tried the LRECL of 2048 (we use 8196) and 'record_separator=none' but the result is the same.

[Peter_C]

perhaps rs=none might work,
but what version of the tagsets.excelxp is reported. It should be in a NOTE: of the format[pre]NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12).[/pre]

[Kwok]

Hi Peter,

Thanks for your info. The only note I got in the log for Excel is

NOTE: Experimental Excel XP tagset. Each table created will go in its own
worksheet.

I also tried the Tableeditor Tagset, it gave us all the options except it ignored the variable statement
VAR ID/STYLE(DATA)={TAGATTR="FORMAT:00000000000000"};
and output the variable as number(1.23457E+13) instead of character (12345678912201).

Any suggestion ?

Thanks

[Cynthia_sas]

Hi:
What version of SAS are you running???? I haven't seen that "experimental" message for a while! To find our your version of SAS, put this at the end of your program:
[pre]
%put SAS Version is: sysvlong4;
[/pre]

I think that you really may need to work with Tech Support on this issue.

TAGSETS.TABLEEDITOR works by a different method than ODS TAGSETS.EXCELXP. TABLEEDITOR creates an HTML file with embedded JavaScript that will load your HTML file in Excel once the HTML file loads into the browser. Sometimes you have to click a button to initiate the load, sometimes, depending on your options, the load into Excel is automatic.

The way Excel treats long numbers is a known issue -- even if you send Excel a number as a character string, Excel uses the "general" format for the string -- which results in scientific notation. I believe that since TAGSETS.TABLEEDITOR uses HTML, that you cannot use TAGATTR to send a Microsoft format to Excel, but instead must use the HTMLSTYLE method with an mso-format, as described here:
http://www2.sas.com/proceedings/forum2008/258-2008.pdf on page 29

cynthia