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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SuzanneDorinski
Lapis Lazuli | Level 10

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

3 REPLIES 3
ballardw
Super User

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.

SuzanneDorinski
Lapis Lazuli | Level 10

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. 

mandonium
Fluorite | Level 6

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

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