Hi
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
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 ' .
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'};
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 ' .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.