The SAS Output Delivery System and reporting techniques

Improper output when exporting to excel using ODS, spaces are removed.

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Improper output when exporting to excel using ODS, spaces are removed.

I'm exporting to an .xlsx file and spaces are being removed.

 

Here's what I get when I first open the file:

Capture1.PNG

 

 

Here's what I get when I remove the "Wrap Text" and expand-to-fit the columns.

 Capture.PNG

 

Here's my code:

data ae0302;
set ae0301;
if aeongyn='' then ls03chk1="Ongoing is missing";
else ls03chk1='';

if aeoutcm='' then ls03chk2="Outcome is missing";
else ls03chk2='';

if (ls03chk1='' or ls03chk2='') and aeongyn='No' and (aeoutcm='Not Resolved' or aeoutcm='Unknown') then ls03chk3="Outgoing and Outcome do not match";
if (ls03chk1='' or ls03chk2='') and aeongyn='Yes' and (aeoutcm='Resolved' or aeoutcm='Resolved with sequelae' or aeoutcm='Death') then ls03chk3="Outgoing and Outcome do not match";
else ls03chk3='';

if ls03chk2='' and (aeoutcm='Not Resolved' or aeoutcm='Unknown') and aeendate ne '' then ls03chk4="AE not resolved or unknown and end date is recorded";
if ls03chk2='' and (aeoutcm='Resolved' or aeoutcm='Resolved with sequelae' or aeoutcm='Death') and aeendate='' then ls03chk4="AE resolved or death and end date is missing";
else ls03chk4='';
run;

data ls0301; format ls03er $255.; set ae0302; by subjid;
	ls03er=catx(', ', ls03chk1, ls03chk2, ls03chk3, ls03chk4);
	drop ls03chk1 ls03chk2 ls03chk3 ls03chk4;
	label ls03er='Error Message'; run;

data list03; format ls03er; merge ds02 ls0301 (in=ls03); by subjid;
	if ls03=1; run;


%let rundate = %SYSFUNC(today(),yymmddn8.);
ods excel file="&path2\03 - ae outcomes_&rundate..xlsx"; 
ods excel options (embedded_titles='yes' sheet_name="Listing 03"); 
proc report data=list03; run;
ods excel close;
ods listing;

Any help would be greatly appreciated.

 

Thank you.


Accepted Solutions
Solution
‎03-19-2018 08:35 PM
Frequent Contributor
Posts: 123

Re: Improper output when exporting to excel using ODS, spaces are removed.

[ Edited ]
Posted in reply to mandonium

See Tips for Using the ODS Excel Destination, by @Chevell_sas.

 

Near the end of the post, Chevell explains how the ODS Excel destination adds a carriage-return line feed when the text wraps. 

 

If you have SAS 9.4 maintenance release 4, you can use the FLOW="TABLES" suboption.  If you are using an earlier version of SAS, specify a width so that the text doesn't wrap. 

View solution in original post


All Replies
Super User
Posts: 13,889

Re: Improper output when exporting to excel using ODS, spaces are removed.

Posted in reply to mandonium

Can you provide a small example data set in the form of data step code that has this behavior. I can't duplicate this with pictures. Also which version of SAS are you running?

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Solution
‎03-19-2018 08:35 PM
Frequent Contributor
Posts: 123

Re: Improper output when exporting to excel using ODS, spaces are removed.

[ Edited ]
Posted in reply to mandonium

See Tips for Using the ODS Excel Destination, by @Chevell_sas.

 

Near the end of the post, Chevell explains how the ODS Excel destination adds a carriage-return line feed when the text wraps. 

 

If you have SAS 9.4 maintenance release 4, you can use the FLOW="TABLES" suboption.  If you are using an earlier version of SAS, specify a width so that the text doesn't wrap. 

New Contributor
Posts: 4

Re: Improper output when exporting to excel using ODS, spaces are removed.

Posted in reply to SuzanneDorinski

Thank you Suzanne, I was able to use the FLOW="tables" suboption and it worked.  Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 165 views
  • 1 like
  • 3 in conversation