The SAS Output Delivery System and reporting techniques

ODS Excel output doesn't match Proc Export output

Reply
New Contributor why
New Contributor
Posts: 3

ODS Excel output doesn't match Proc Export output

I am trying to move from one method of outputting data to an excel file (proc export) to another (ods excel).  I am outputting to a UNIX server.  When I use the ods excel method and import the file back in to SAS to compare, there is the character string " " in the place of every blank (example: y z is instead formatted as y&#x00a0z).  Similarly, searching for "y z" in excel does not find the value in the ods excel file.  Is there an option when outputting with ods excel to treat blanks as normal blanks (ASCII 32 instead of 255)?

I am using 9.4M4 and the ods excel code is below:

ods excel file="output.xlsx"
    options(sheet_interval='none' suppress_bylines='no' sheet_name='All Items'
            absolute_column_width='40,40,40,25,25,25,25,25,25,25,25,25,25,25' absolute_row_height='15')
;

proc print data=DATASET noobs label
style(header)={FONT_FACE='Albany AMT' FONT_WEIGHT=medium BACKGROUND=#FFFFFF COLOR=black}
style(column)={FONT_FACE='Albany AMT' asis=on width=1000% TAGATTR="Type:String"}
;
run;

ods excel close;
SAS Super FREQ
Posts: 9,425

Re: ODS Excel output doesn't match Proc Export output

Hi:

  Here's what I observed without ASIS=ON, the FIND in Excel worked to find the string y z:

without_asis.png

But, using the SAME data and just adding ASIS=ON to the style override, caused the search string NOT to work in Excel:

with_asis_not_found.png

 

So I am thinking that unless you absolutely need ASIS=ON to take it out.

 

Otherwise, I suggest you open a ticket with Tech Support if you can't live without ASIS=ON.

 

Cynthia

New Contributor why
New Contributor
Posts: 3

Re: ODS Excel output doesn't match Proc Export output

Posted in reply to Cynthia_sas

Thank you, Cynthia.  In fact, we have only explored moving to ODS Excel because using proc export resulted in losing leading or trailing blanks in the data.  The asis=on option was the key to resolving this issue, we thought.

 

I replicated your results and had no issues upon re-importing when removing the asis=on.  I'll follow up with Tech Support.

 

 

Super User
Posts: 13,920

Re: ODS Excel output doesn't match Proc Export output

You may have other issues if that actually how you export data with proc print. Numeric values that are reimported become character and depending on ranges of value may have things like  84.0 when the original value was 84.0. It appears that any value that would be right aligned and is shorter than the longest value, such as weight in the sashelp.class data set, where some are 150.0 any value less than 100 came back with that leading string. Removing ASIS=on does remove the the &#x00z0: but the values are still character and not numeric when reimported into SAS using Proc Import.

Ask a Question
Discussion stats
  • 3 replies
  • 172 views
  • 0 likes
  • 3 in conversation