BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi

I'm using the excelXP tagset. I would like to have a space after the value in tabulate data cells. They are a bit too close when printed. With data style containing tagattr='format:#,##0" ";[Red]-#,##0" "' I can easily add a trailing blank. However, all datacells are "wrapping" and this causes that trailing blank to be ignored.
Does anyone know how to switch off the wrapping for data cells, or some alternative way to get a space between the value and the cell boundary?

PeterC
13 REPLIES 13
OS2Rules
Obsidian | Level 7
Peter:

Had exactly the same problem.

Firstly, make sure the cell with is wide enough for the value being written - we use the absolute_column_width parameter of the Excelxp tagset to set each column.

If that doesn't fix the problem, then you need to turn off the wrap 'option' on the text being written to the cell. We made a change to the ExcelXP tagset to turn off the text wrap on only the data cells and this worked. We also received a piece of code from SAS that did the same thing by making a modification to the tagset from the program being run (using PROC TEMPLATE).

As far as the print being too close to the right cell border - SAS support says that this is an Excel problem. We tried putting the space in the format but this caused us to have problems with the alignment of the values in the cell. (This only appears on the print, the screen view and print preview have the text further from the cell boundary).

Hope this helps.
deleted_user
Not applicable
thank you for the encouragement
~ guess I'm on the right lines with absolute column width ~~~

Is there any chance you would provide a link to the solutions you found ? I couldn't find them searching the forum.

Peter
OS2Rules
Obsidian | Level 7
Peter:

Here is the code that was sent to us by SAS:

proc template;
define tagset tagsets.test;
parent=tagsets.excelxp;
define event align_tag;
start:
break /if $align_tag;
put ' do /if cmp($cell_class, 'systemtitle');
put ' ss:Wraptext="0"';
else /if contains($cell_class, 'data');
put ' ss:WrapText="0"';
else;
put ' ss:WrapText="1"';
done;
set $align_tag "True";
finish:
break /if ^$align_tag;
putl '/>';
unset $align_tag;
end;
end;
run;


This code makes a change to the WrapText so that the data cells in the Excel spreadsheet do not wrap text.
OS2Rules
Obsidian | Level 7
Well....

That didn't work, did it.

Someone know how I can post the code without the editor f***ing it up?
deleted_user
Not applicable
Dear Rules

thank you for sharing that piece of code. Let me return the favour, and indicate how you can format for code :

try [pre]
data ;
infile ;
input ;
run;
[/pre]

you place [ pre ] as preceding tag and [ /pre ] as closing tag
but without the spaces embedded inside those [ ] square brackets

PeterC
OS2Rules
Obsidian | Level 7
Thanks Peter.

Problem is that my code contains forward slashs and the when the editor hits the first one the rest of the code goes missing. Even with the tags before and after.

Now what ....
Cynthia_sas
SAS Super FREQ
here is some code with slashes inside a [ pre ] and [ /pre ] (remember that the 5 characters [ pre ] do not contain ANY spaces):

[pre]
here's a forward slash twice / in this code snippet
define something / group
style(column) = {foreground=purple};

now, here's a backslash \ just in case
[/pre]

You MUST use (pre) and (/pre) before and after the code, but where I have parentheses, you MUST use square brackets. [ ] IF your code has any greater than or less than symbols, you MUST refer to those with their named entity versions < and >
cynthia

cynthia
OS2Rules
Obsidian | Level 7
Cynthia:

Thanks for the instructions, but ...

I still have the formatting problem. I followed your instructions and added the code between the tags (with the square brackets). When I "Preview" the code, it ends at the same place as happened above, which is somewhere near the middle of the code.

I'm no expert, but methinks that there is something in the code sample that if mucking up the formatting.
Cynthia_sas
SAS Super FREQ
Hi,
The ONLY other thing that could mess up inside the [ pre ] section would be greater than and less than symbols. Since you're talking about a tagset that produces XML, those symbols will be all over the place and the work to protect them will be labor intensive (you'd have to turn every > into &gt; symbol and every < into &lt; symbol).

I don't know whether there is a way to attach a file to the post messages. Otherwise, the only way to send/receive code would be for Patrick to contact Tech Support and reference your tracking number and ask for the same code or for Patrick to give you his email address and for you to email the code directly.

cynthia
deleted_user
Not applicable
I have asked our UK SAS Customer Support to obtain that code. They're searching. The search would be easier with a tracking number for their original solution.

PeterC
OS2Rules
Obsidian | Level 7
Sorry for the delay:

Here is the track: Trknum: us6592415

Hope that helps.
OS2Rules
Obsidian | Level 7
I'm going to try the code again with Cynthia's changes:

[pre]
proc template;
define tagset tagsets.test;
parent=tagsets.excelxp;
define event align_tag;
start:
break /if $align_tag;
put '<Alignment';
do /if cmp($cell_class, 'systemtitle');
put ' ss:Wraptext="0"';
else /if contains($cell_class, 'data');
put ' ss:WrapText="0"';
else;
put ' ss:WrapText="1"';
done;
set $align_tag "True";
finish:
break /if ^$align_tag;
putl '/>';
unset $align_tag;
end;
end;
run;

ods tagsets.test file="c:\temp.txt";

proc print data=sashelp.class;
run;
[/pre]

Hope this works... the "preview" liked it this time.
deleted_user
Not applicable
Many thanks to all who have provided the help and support : Cynthia, OS2_Rules, and some support from SAS Customer Support in UK

The only proc template excelXP override which I found to work and produce valid xml, was the final code OS2_rules was able to generate.

The unexpected feature I found I had to add, appears in my tabulate style code on the proc statement[pre]
proc tabulate style= [ tagattr='format:#,##0"" "";[Red]-#,##0"" ""']
[/pre]
(That style element affects the data cells of analysis variable statistics.)
Normally we might add a trailing character in an excel custom format like
[pre] 'format:#,##0" ";[Red]-#,##0" "' [/pre]
However this style element gets inserted within "double quotes" and I think the interpretation of a pair of ", within a pair of " gets confused and so ignored.

This was the (less effective) xml [pre] <NumberFormat ss:Format="#,##0" ";[Red]-#,##0" "" /> [/pre]
When the extra quotes are added, the result looks better, and it works for me ! [pre] <NumberFormat ss:Format="#,##0"" "";[Red]-#,##0"" """ />[/pre]

again, many thanks

PeterC

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1868 views
  • 0 likes
  • 3 in conversation