topic Re: Output displays Question mark "?" in ods tagsets.excelxp using picture format in ODS and Base Reporting

Output displays Question mark "?" in ods tagsets.excelxp using picture format

sasuser101 — Tue, 01 Dec 2015 03:44:26 GMT

i have simple proc report where i calculate median for a given variable and than use picture format with round option to round it.

Using ODS i save results in excel and it shows no issue when i run code in interactive mode.

But when i run same code in non interactive mode (sas unix) i see "?" with values where i applied picture format.

Example 48.24?

Is there a way to get rid of "?" for values from output. Below is sample code for reference.

i am using unix sas 9.2. Use exceed to run in interactive mode.

Sample code

proc format;

picture jjc (round)

Low-high = ‘00.00’;

run;

%MACRO Report_N;

Proc report data= work.Median nowd missing;

columns A B C D E ;

define A / order Group;

define B / analysis sum;

define C/analysis median format=jjc.;

define D/analysis median format=jjc.;

define E/analysis median format=jjc.;

compute after;

&Class='Total';

endcomp;

rbreak after / summarize dol dul ;

run;

%MEND Report_N;

/******** Export Results to Excel **************/

ODS tagsets.excelxp FILE="&outdir.&File_Name..xls";

/* Tried both tagsets and ods html both shows "?" */

filename out1 "&outdir.&File_Name..xls";

ods html body=out1 style=styles.noborder;

%Report_N;

Thanks

Re: Output displays Question mark "?" in ods tagsets.excelxp using picture format

RW9 — Tue, 01 Dec 2015 09:31:11 GMT

First question, why do you need a format for that, just round()?

Secondly its likely that a special character is being interpreted. This gets a bit tricky, and the cause is likely to be Excel. Firstly, what does the underlying XML look like, open it in a proper hex editor and get the hex number of the character - > Excel does a great job of interpreting things behind the scenes, so don't trust what it is showing you.

Remember also that between the two operating systems there is differences, end of line for instance on Win is LF+CR, its only CR on Unix for example.

A final thing to do is to specify the format on the report:

define variable / "Variable" style={tagattr='format:0.00'};

Re: Output displays Question mark "?" in ods tagsets.excelxp using picture format

ballardw — Tue, 01 Dec 2015 16:34:29 GMT

You don't mention what your input data is but I think your picture needs work.

With this code:

data _null_;
   input x;
   put x= jjc.;
datalines;
1
.1
100
100.5
;
run;

I get output of:

x=1.00’
x=10’
x=
x=50’

I suspect that Excel doesn't like the trailing ' .

Re: Output displays Question mark "?" in ods tagsets.excelxp using picture format

sasuser101 — Tue, 01 Dec 2015 23:37:47 GMT

The issue got resolved by fixing single quote on picture format. Apparently looks like single quote was treated as different character as it was tilted kinda 45 degrees(might be copied from different application by previous developer). i deleted and reentered single quote and i didn't see question marks in output. Thanks to your replies.