BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
13 REPLIES 13
Peter_C
Rhodochrosite | Level 12
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
data_null__
Jade | Level 19
> 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.
Peter_C
Rhodochrosite | Level 12
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.
data_null__
Jade | Level 19
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.
Eric_SAS
SAS Employee
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
deleted_user
Not applicable
Any idea when and where the update will appear?
deleted_user
Not applicable
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.
Peter_C
Rhodochrosite | Level 12
Dirkvans

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

peterC
Peter_C
Rhodochrosite | Level 12
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)
Cynthia_sas
SAS Super FREQ
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
Peter_C
Rhodochrosite | Level 12
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
Eric_SAS
SAS Employee
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];
Eric_SAS
SAS Employee
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1778 views
  • 0 likes
  • 5 in conversation