BookmarkSubscribeRSS Feed
why
Calcite | Level 5 why
Calcite | Level 5

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;
3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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

why
Calcite | Level 5 why
Calcite | Level 5

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.

 

 

ballardw
Super User

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.

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