04-03-2014 10:29 AM
Could you clarify with an example? Your proc report within the ods tageset open/close should print all text out, however its not a given that what Excel displays is the same as witihin SAS.
04-03-2014 10:33 AM
the problem is that it is not text, but numeric.
and all missing values, wether normal or special, show in excel as .
in SAS i am fine.
I will post an example tomorrow.
04-03-2014 10:38 AM
Perhaps you could convert the column to character prior to exporting to Excel? Will wait to see example.
attrib test format=8.;
data want (drop=test);
attrib text_for_export format=$10.;
04-04-2014 06:48 AM
Sorry, not sure what your example is trying to do. I have changed your code slightly to get it working, however the output from the tabulate looks pretty much identical to the input dataset other then three new variables _TYPE_, _PAGE_, _TABLE. Also I note that the proc tabulate itself is not honoring the missing value per the dataset as you have no indicator - only for the datastep. So try using "options missing=B" at the start of your program.
This code works and shows 'B' in the output, it does this by putting the value into a character variable and using that. (Note you can use the out= from the proc tabulate, and then post process that before outputting to Excel).
input v_id $ v_value;
attrib my_val format=$10.;
ods _all_ close;
ods tagsets.excelxp file="S:\Temp\Rob\test.xls" style=statistical options (frozen_headers="1" absolute_column_width="none" zoom="80");
ods tagsets.excelxp options (sheet_name="Test");
proc report data=work.inter nowd split="~"
style(header)=[background = #DBE5F1 foreground = #000099 font_weight = bold ]
style(column)=[just=l borderrightcolor=white borderleftcolor=white
borderleftwidth=1 bordertopcolor=white borderbottomcolor=white
borderrightwidth=1 bordertopwidth=1 borderbottomwidth=1];
columns v_id my_val;
define v_id / "Id";
define my_val / "Value";
ods tagsets.excelxp close;
04-04-2014 07:10 AM
Sorry if I did not show exactly where my problem is. I need a numeric field in excel in a specific format. To get this, the xml written by SAS needs to show "real number" in most places, but specific Characters (such as . , X, B) in others.
I hoped to achieve this with special missing values, but cant get it working.
P.S. I just missed this line while copying my code:
04-04-2014 08:30 AM
I am afraid I don't know of a direct way of doing that. The specific missing character functionality you are talking about is a SAS specific item, Excel does not have that as missing is just missing. So from before, you would need to convert it character for Excel to show characters. The only other options I can think of is:
- Change your actual missings to a specific number which can't happen, e.g. -999999.
- Post process the XML file which you generated, so read it in as a text file and search for:
<Cell sstyleID="data__l1" ss:Index="1"><Data ss:Type="Number"> xx </Data></Cell>
Then write your specific character in where the xx is.
- Alternatively use Excel to replace blanks after export.
04-04-2014 10:11 AM
you might have success using traffic lighting to set the background formatting for distinctive missing values
and remembering that the excel custom format allows separate custom formats (separated by semi-colons) for positive negative zero and text values.
However this approach wouldn't distinguish between different special missing values
04-04-2014 08:41 AM
Lateral answer for your "nicely" question
My trick is to copy the sas colored code from the pgm file to a new temporary word file without too much space gap between lines
then in a second step copy again the colored code from word to here