topic Re: Question on in table in SAS Procedures

Question on in table

vomer — Mon, 25 Feb 2013 16:22:00 GMT

Hi All,

I keep getting the following in my tables in sas (in the autofilter):

http://i.imgur.com/r5tjKcr.png

I have a total at the end of the table which is causing this - any way to remove/hide it?

Re: Question on in table

AncaTilea — Mon, 25 Feb 2013 16:26:46 GMT

could you provide a snippet of your code.

I am assuming this is a proc report?

Re: Question on in table

vomer — Mon, 25 Feb 2013 16:39:17 GMT

Actually, I am using proc print:

proc print data=testdata noobs label;
   var Org Unit N;
   where Org="Org1";
   sum N;
   label N="Number of x";
   label Org="Organization";
   label unit="Unit";
run

Re: Question on in table

Reeza — Mon, 25 Feb 2013 16:42:03 GMT

What do you mean in autofilter? Are you using EG?

Re: Question on in table

vomer — Mon, 25 Feb 2013 16:43:12 GMT

I am using tagsets, so the ables have an autofilter at the top:

style=sasweb
options(toc_background="white"
           toc_expand="yes"
            toc_print="yes"
autofilter="yes"
filter_cols="1,2,3"

Re: Question on in table

AncaTilea — Mon, 25 Feb 2013 16:52:47 GMT

What happens when you run this code:

ods tagsets.excelxp file = "\\your_path\to_test.xls" style = sasweb

options(autofilter="yes" );

proc print data=sashelp.class noobs label;

var name height;

where age=12;

sum age;

label age="Age";

run;

ods tagsets.excelxp close;

You note I removed the toc_...options because SAS said unrecognized option...

Does this work?

PS: change the path where the file is saved

Re: Question on in table

vomer — Mon, 25 Feb 2013 17:33:23 GMT

Nop, same "&nbsp" shows up in the filter. :smileycry:

Re: Question on in table

Reeza — Mon, 25 Feb 2013 17:37:22 GMT

What version of Excel are you using?

Post your full code and log or a smaller sample if it generates the problem.

Re: Question on in table

vomer — Mon, 25 Feb 2013 20:55:58 GMT

I am not using excel - the output is done in HTML file.

Re: Question on in table

Reeza — Mon, 25 Feb 2013 20:58:32 GMT

You need to post your full code and log of what you're doing. We're really just trying to guess without it and its a waste of time for us and yourself.

Re: Question on in table

AncaTilea — Tue, 26 Feb 2013 12:38:03 GMT

Hi Vomer,

Would you please post the code?

Thanks.

Anca.

Re: Question on in table

vomer — Tue, 26 Feb 2013 15:01:01 GMT

Sure:

ods tagsets.tableeditor file="file1.html"
path="PATH\HERE" (url=none)
style=styles.button
options(toc_background="white"
           toc_expand="yes"
            toc_print="yes"
autofilter="yes"
filter_cols="1,2,3"
AUTOFILTER_ENDCOL="3"
frozen_headers="yes"
pageheight="400"
autofilter_width="15em"
);
title1 'Number of Clients';
proc print data=testdata noobs label;
   var Org Unit N;
   where Org="Org1";
   sum N;
   label N="Number of x";
   label Org="Organization";
   label unit="Unit";
run;
ods tagsets.tableeditor close;

Re: Question on in table

AncaTilea — Tue, 26 Feb 2013 15:25:23 GMT

So, in the SAS Output wahtever you are trying to print will look (more or less) like this:

Age

Height

12	57.3
12	59.8
12	59.0
12	56.3
12	64.8
	======
	297.2

The 'problem' is the line ===== which translate to a non blank space (nbsp) (I may over-reaching here)...but when you try to filter your data, you also have this option...the ====

So, I need to read/google more to figure out how to 'restrict' it from showing in your html...

more later.

Re: Question on in table

vomer — Tue, 26 Feb 2013 15:56:33 GMT

Hi Anca, Yes I think it is that extra line that is causing the "space" to appear. Hope you can find a solution - I could not find anything so far

Re: Question on in table

AncaTilea — Tue, 26 Feb 2013 19:44:57 GMT

Hi,

would you please try this addition to your existing code:

ods tagsets.tableeditor file="file1.html" event = cell_is_empty

path="\\yourPath..." (url=none)

style=styles.button

options(toc_background="white"

....rest of code....

I think it may work!

Actually it will only work if you are summing over all the variables you are printing...

Message was edited by: Anca Tilea

Re: Question on in table

vomer — Wed, 27 Feb 2013 16:13:10 GMT

Hi Anca, the trouble is that my table is setup with names and numbers. Since I cannot "sum" an organization name it looks like that does nothing

Re: Question on in table

AncaTilea — Wed, 27 Feb 2013 16:44:16 GMT

Right, understood.

This is how far I got yesterday;

You want to be able to 'manipulate' (autofilter) a table that looks like this:

Age Weight Name

12 100 Mia

13 100 Tia

11 100 Elvis

== ===

36 300

What this could translate to:

Age Weight Name

12 100 Mia

13 100 Tia

11 100 Elvis

== ===

36 300 &nbsp <-- which in SAS doesn't show up bc SAS know that is a blank character space something (my understanding is rather limited), BUT when you 'transfer' it to HTML...the HTML 'interpreter" doesn't know what "&nbsp" stands for, hence it "shows" as an option...

So...there are (hard) ways to modify the tableeditor template such that when there is "blank" to not replace it with (SAS friendly) "&nbsp"...

So my next step is to google autofilter tables created from SAS and figure out how to suppress the &nbsp...

OK, I shall be back.

Re: Question on in table

AncaTilea — Wed, 27 Feb 2013 18:04:40 GMT

Ok,Vomer.

I am going to end here with my final findings

(I think you should have SAS look into this).

I don't think there is a simple way for you to have "&nbsp" not show.

BUT

you could run the following piece of code:

proc template;

source tagsets.tableeditor/file = "your_path/to_edit.sas";

run;

This code will save the template of tagsets.tableeditor that you are currently using.

ok.

So open that SAS file (saved in your_path/to_edit.sas) (you will notice it is very long but don't despair).

First write PROC TEMPLATE; on the first line.

Then you will see 'define tagset tagsets.tableeditor....change tableeditor to vomer_table

(You don't want to overwrite the existing tableeditor template)

OK.

Then CTRL+F and "&nbsp" and on line 4207 you see it there...? and on line 4210.

These are the only two times it shows in the template.

So, what I've been trying to do, is edit this, save it as my own table_editor version and re-run the proc print with my template.

It doesn't work.

So, this is way above my head!

Best of luck and please do let us know if you find the solution (as I will,too).

Cheers!

Anca.

Re: Question on in table

AncaTilea — Thu, 28 Feb 2013 13:30:44 GMT

Hi again.

So, here is my work-around:

First perform a PROC MEANS or PROC SUMMARY with the SUM option, save the values int a data set (using ODS Output Summary = temp)..

Then do some manipulations such as:

if org = "" then org = "Total Profit"...or whatever you need, so at the end you get a table like this:

name	Age	Height

Alice	13	56.5
Barbara	13	65.3
Carol	14	62.8
Jane	12	59.8
Janet	15	62.5
Joyce	11	51.3
Judy	14	64.3
Louise	12	56.3
Mary	15	66.5
Total Age	119	.

I haven't yet come up with a smart way to give height a value if "."....

Here is my code:

	proc means data = sashelp.class sum;
	var age;
	where sex = "F";
	ods output Summary = s;
	run;

	data to_print(keep = name age height);
	length name $10;
	set sashelp.class(where = ( sex = "F")) s;
	if name = "" then name = "Total Age";
	if age = . then age = age_sum;
	run;

ods tagsets.tableeditor file = "to_dlt.html"

	path="\\cumulus\share\atilea" (url=none)
	style=styles.default
	options(toc_background="white"
	toc_expand="yes"
	toc_print="yes"
	autofilter="yes" filter_cols="1,2,3" AUTOFILTER_ENDCOL="3" frozen_headers="yes"
	pageheight="400" autofilter_width="15em");

	proc print data=to_print noobs label;
	run;

ods tagsets.tableeditor close;

Re: Question on in table

vomer — Thu, 28 Feb 2013 14:47:58 GMT

Hi Anca, thanks so much for investigating. I will try the first custom tagset method and see if I get anywhere. Looks like I can call on a custom table template using the %include statement.

Perhaps I should also contact sas regarding this issue as well as you suggested.