BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser101
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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 ' .

 

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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'};

 

ballardw
Super User

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 ' .

 

sasuser101
Obsidian | Level 7
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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