The SAS Output Delivery System and reporting techniques

Issues with the ODS Excelxp tagset and SAS 9.2

Reply
N/A
Posts: 0

Issues with the ODS Excelxp tagset and SAS 9.2

I have run up against the following problems with the Excelxp tagsets and SAS 9.2. Some are well known but have not yet, as far as I know, been satisfactorily addressed:

1 Proc Freq: Crosstabs yield empty tables;

2 Proc Means: Specifying more than one variable leads to the results for all the variables in one row;

3 Procs Univariate, Reg and possibly others: The variable(s) analysed are not specified in the output; and

4 Proc Lifetest: With a class statement, the level of the class reported on is not given.

A few work-arounds that I have managed to come up with are:

1 Proc Freq: Run it with a crosslist option and do some post-editing on the spreadsheet. Crosslist at least gives the row and column percentages that the List option did not.

2 Proc Means: Here I have two options namely:

a) Specify a set of one-variable Proc Means and give the variable analysed in a Title statement. If you want them all in one spreadsheet, specify:

ods markup tagset = Excelxp options (sheet_interval = “None”);

as an option for the first Proc Means.

b) This one is more elegant. Transpose the variables of interest into one column as follows:

Proc transpose data = XXXX out = XXXX1 name = var;
var var1 var2 var3;
run;
Proc means data = XXXX1;
Class var;
Var col1;
Run;

This is useful if you want sets of means for different classes for example age, height, weight, etc for genders, etc.

3 Proc Univariate, etc: As in 2a) above, specify a set of procedures with one variable in each, with the variable name in a Title statement. Again possibly specifying sheet_interval = “None” to get them all in one spreadsheet.

4 Proc Lifetest: The only solution I have here is to copy the:
Summary of the Number of Censored and Uncensored Values
table given at the end of the output into the beginning of the sheet and then cut and paste every class level’s row into the top of that level’s output.

I would appreciate it if someone with more expertise in programming tagsets than myself, could address these problems.
Valued Guide
Posts: 2,177

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to deleted_user
re:
> 2 Proc Means: Here I have two options namely:
> a) Specify a set of one-variable Proc Means and give
> the the variable analysed in a Title statement. If you
> want them all in one spreadsheet, specify:
> ods markup tagset = Excelxp options (sheet_interval =
> “None”);
> as an option for the first Proc Means.
> b) This one is more elegant. Transpose the variables
> s of interest into one column .....
......

another approach for this proc was offered by Myra Oltsik (and some old hack ;-) )This approach makes just a single pass through the data. It is described in the paper at http://www2.sas.com/proceedings/sugi31/059-31.pdf Paper 059-31 : A Better Means ? The ODS Data Trap
Respected Advisor
Posts: 3,799

Re: Issues with the ODS Excelxp tagset and SAS 9.2

> another approach for this proc was offered by Myra
> Oltsik (and some old hack ;-) )This approach makes
> just a single pass through the data. It is described
> in the paper at
> http://www2.sas.com/proceedings/sugi31/059-31.pdf
> Paper 059-31 : A Better Means ? The ODS Data Trap

Be careful if you expect two-tailed CLMs. The macro can only produce one-tail CLM, even when you ask for both.

Reference
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#/documentation/cdl/en...

Tip: Use CLM or both LCLM and UCLM to compute a two-sided confidence limit for the mean. Use only LCLM or UCLM, to compute a one-sided confidence limit.

This is TIP is not repeated for OUTPUT statement but the same rules apply.
Valued Guide
Posts: 2,177

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to data_null__
those authors probably have to insert a caveat about this issue on any future release.
Of course, the technique in the macro referred, could be entirely replaced with /autoname and a reconstruction of the names into and out of autoname handling (to avoid trying to squeeze a column name and statistic name within a 32 width limit when nothing stops an input name being too wide for the squeeze). those authors will have to confer.
Respected Advisor
Posts: 3,799

Re: Issues with the ODS Excelxp tagset and SAS 9.2

You can keep the code mostly like it is and not involve AUTONAME (too messy) using dummy variables in the output statement. You need to know how many variables to avoid warnings.

[pre]output out=LCLM LCLM= UCLM=dummy1-dummy(N);[/pre]

That's how I do it. I do have to figure out how may variables are in the VAR list. That can be a bit tricky if you want to have a program that has same default as PROC MEANS, to analyze all numeric VARS not mentioned elsewhere.
SAS Employee
Posts: 95

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to deleted_user
The latest version of the ExcelXP tagset addresses the problems with stacked columns you are experiencing with Proc freq and Means.

The other problems are because procedure titles are completely ignored by the tagset. This is a known problem and is in the queue to be fixed in the future.

The latest version is undergoing some optimizations to make the stacked column and vertical measurement processing faster, when that is done it will be available on the web.

Eric
N/A
Posts: 0

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to deleted_user
Any idea when and where the update will appear?
N/A
Posts: 0

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to deleted_user
I have just downloaded and tested the latest V1.114 Excelxp tagset and none of my problems mentioned above had been addressed. Crosstab tables are still empty; multiple variables in proc means still appear in one row; and multiple variables in proc univariate are not identified. Pity.
Valued Guide
Posts: 2,177

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to deleted_user
Dirkvans

thank you for referring to 1.114
I didn't know it had arrived!
At last.

peterC
Valued Guide
Posts: 2,177

Re: Issues with the ODS Excelxp tagset and SAS 9.2

a small issue with 1.114 (I think)
obsheader seems to be handled differently

proc print label data= sashelp.class style(header obsheader)=TMheader;

no longer provides "obsheader" with the vertical alignment requested by the style TMheader :-( but this style works for the regular headers.

where style TMheader was defined as

proc template;
define style Styles.myXstyle;
parent = styles.default;
style TMheader from Header /
vjust = T
just = C;
end;
run;

Now that "from History of changes for tagset" v1.104, 04/02/09
Added vjust as an honored over ride.
should I be using a different mechanism for vertical alignment of OBSHEADER?

peterC
(discovered on SAS913 sp4)
SAS Super FREQ
Posts: 8,864

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Hi, Peter:
Just curious, what happens if you specify them separately???
[pre]
proc print label data= sashelp.class
style(header)=TMheader
style(obsheader)=TMheader;
[/pre]

I just wondered whether specifying them separately made a difference. (I haven't downloaded 1.114 yet.)

cynthia
Valued Guide
Posts: 2,177

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to Cynthia_sas
Thank you Cynthia
to package a demo, I finally made sure to re-create the style within my process. Hey-presto! no longer a question of doubt!
I had jumped to the wrong assumption - end up with "egg on face".
My routines no longer differ between 1.86 and 1.114 and neither do the results!
Nothing to do now but apologise to any who thought I was offering a reason not to adopt the new version. Sorry. ....

peterC
SAS Employee
Posts: 95

Re: Issues with the ODS Excelxp tagset and SAS 9.2

I would have expected that to work. The fix in v1.104 is to allow something like this in proc print.

var foo /style=[vjust=T];
SAS Employee
Posts: 95

Re: Issues with the ODS Excelxp tagset and SAS 9.2

Posted in reply to deleted_user
Version 1.116 should be on the web soon. It has been submitted and there are no holdups before it can be published on the web.

This version fixes a problem with footnotes going missing in rare cases, and it turns on stacked columns so that proc means will now stack it's values, it also fixes the problem of missing values with Freq crosstabs at SAS 9.2.

Univariate will still not identify multiple variables because the tagset still does not support proc titles. Supporting proc titles is a major undertaking at this point and is unlikely to happen before we have support for Excel 2007-2010.
Ask a Question
Discussion stats
  • 13 replies
  • 651 views
  • 0 likes
  • 5 in conversation