topic Re: Improper output when exporting to excel using ODS, spaces are removed. in ODS and Base Reporting

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

mandonium — Mon, 19 Mar 2018 19:02:28 GMT

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

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

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

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.

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

ballardw — Mon, 19 Mar 2018 22:13:29 GMT

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-data-AKA-generate/ta-p/258712 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.

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

SuzanneDorinski — Mon, 19 Mar 2018 22:21:07 GMT

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.

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

mandonium — Tue, 20 Mar 2018 00:36:16 GMT

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