topic Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing % in SAS Procedures

1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

jcis7 — Wed, 19 Sep 2018 21:49:01 GMT

ods excel file="F:\want.xlsx" style=journal;
ods excel options(sheet_name='Want' autofit_height='yes' width_fudge='0.58');

proc report data=want nowindows headline headskip ;
	column location ('DATE' date ) ('TOTAL' total)  ('ALL' all) 
                    ('STUDENTS WITH VAX1' vax1)  ('STUDENTS WITH VAX2' vax2)
                    (STUDENTS WITH VAX3' vax3)  ('STUDENTS WITH VAX4' vax4);
	define location / group order;
	define date / group order=internal descending ' '; 
	define total / analysis 'NUMBER' format=comma8.;
    define vax1 / display right 'PERCENT' format=percent7.2 
           style(column)={tagattr="format:##0.00\%"};
	define vax2/ display 'PERCENT' format=percent7.2 
           style(column)={tagattr="format:##0.00\%"};
    define vax3 / display 'PERCENT' format=percent7.2 
           style(column)={tagattr="format:##0.00\%"};
    define vax4 / display  'PERCENT' format=percent7.2 
           style(column)={tagattr="format:##0.00\%"};
	define vax5 / display 'PERCENT' format=percent7.2 
           style(column)={tagattr="format:##0.00\%"};  
	compute after location; line ' ';	endcomp;
	
run;  

ods excel close; 
run;

I have three questions:

1) How do you get SAS not to display the % sign in the SAS output? The vaccine variables need to be multiplied by 100 to get a percent but how can I not display the % sign?

2) I was trying to get SAS to multiply the vax variables by 100 but they ended up being displayed in the Excel output as 10000.00% instead of 100% or 9500% instead of 95.00% even though the actual value is 0.100 and 0.95. Am I doing something wrong?

2) Some of the text in the variable 'Location' is made up of two words. When SAS exports into Excel, the location names with two words don't stay on the same line but overflows onto the next line (same cell in Excel, not a new row) and the row height is doubled. How do you keep the text on the same row so all row heights are equal?

Thanks!

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

Haris — Sat, 22 Sep 2018 16:25:25 GMT

If you don't want a percent, don't apply percent format.

Excel has a word-wrap feature. You can either disable that or increase the column width for LOCATION.

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

jcis7 — Tue, 25 Sep 2018 17:00:31 GMT

Thanks. I tried disabling the word wrap feature in Excel and the words no longer wrapped. However, some of the spaces between the two or three word locations were missing. Is there a way to get around that?

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

Haris — Tue, 25 Sep 2018 17:33:31 GMT

That's sounds specific to your data which I don't know.

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

SuzanneDorinski — Wed, 26 Sep 2018 14:02:22 GMT

If you are using SAS 9.4M4 or later, you can use the FLOW= suboption.

@Chevell_sas wrote a blog post that mentions the text wrapping issue. See Tips for Using the ODS Excel Destination for more details.

Example code:

%let folder=/folders/myfolders/ODS Excel examples;

* some of the player names in SASHELP.BASEBALL are long.  in example 
* below, some player names will wrap in Excel output.  if you disable
* wrap text in Excel, you will notice that some spaces are lost.  player
* names are Lastname, Firstname.  the names that were wrapped lose the
* space between the comma and the Firstname. ; 

ods excel file="&folder/long_text.xlsx"
  options(sheet_name='Using defaults'
          index='on');

proc report data=sashelp.baseball;
  column name team league division natbat nhits nruns nrbi;
run;

* flow= option is new for SAS 9.4M4.  for more details, see 
* https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/ ;

ods excel options(flow='tables'
                  sheet_name='Using flow option');
  
proc report data=sashelp.baseball;
  column name team league division natbat nhits nruns nrbi;
run;


* ok, long names no longer wrap, but now the rows where the names had
* wrapped are bigger than the other rows.  use row_height= option.  
* there are 7 parameters for row_height.  0 means use default from 
* style. ;

ods excel options(flow='tables'
                  row_heights='0,14,0,0,0,0,0'
                  sheet_name='Using flow and row_height');
  
proc report data=sashelp.baseball;
  column name team league division natbat nhits nruns nrbi;
run;

ods excel close;

The screen shot below shows the player's name column with text wrapping.

Screen shot showing long text wrapping in ODS Excel output

If you disable text wrapping in Excel, you may notice that you lose some blank spaces in the output. Player names are listed as Lastname, Firstname. The text that had wrapped loses the space between the comma and the first name. The screen shot below shows the name that had wrapped after I disabled text wrapping in Excel.

Screen shot showing long text after disabling text wrapping in Excel

The flow suboption prevents the long names from wrapping, but now the row height for long names appears to be twice the row height of the other rows.

Flow suboption prevents text wrapping, but some rows are twice as tall as other rows

Using the row_height suboption with the flow suboption produces nice output.

Screen shot showing long text not wrapping, all rows in body of report have same row height

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

jcis7 — Wed, 26 Sep 2018 19:19:31 GMT

Thank you!

I tried the example you gave and I'm getting the following messages in the log:

70 ods excel file="&folder\long_text.xlsx"
71 options(sheet_name='Using defaults'
72 index='on');
WARNING: ODS Package ID: DEFAULT does not exist, no action taken.
73
74 proc report data=sashelp.baseball;
75 column name team league division natbat nhits nruns nrbi;
76 run;

WARNING: ODS Package ID: ( ULT does not exist, no action taken.
WARNING: ODS Package ID: ( ULT does not exist, no action taken.
WARNING: ODS Package ID: ( ULT does not exist, no action taken.
WARNING: ODS Package ID: ( ULT does not exist, no action taken.
WARNING: ODS Package ID: ( ULT does not exist, no action taken.
WARNING: ODS Package ID: ( ULT does not exist, no action taken.

WARNING: ODS Package ID: ( ULT does not exist, no action taken

Then I tried separating the options into a separate statement and the warning disappeared:

ods excel file="&folder\long_text.xlsx";
ods excel options(flow='tables'
row_heights='0,14,0,0,0,0,0'

sheet_name='TableWant');

But, when I followed the example you gave without the flow option, there actually was no word wrap, strangely.

When I did try using the flow option, the log said:

Tageset option: flow not recognized:

87 ods excel options(sheet_name='Using defaults'
88 index='on');
89
90 proc report data=sashelp.baseball;
91 column name team league division natbat nhits nruns nrbi;
92 run;

NOTE: There were 322 observations read from the data set SASHELP.BASEBALL.
NOTE: PROCEDURE REPORT used (Total process time):
real time 1.42 seconds
cpu time 1.34 seconds

Tagset Option: flow Not recognized.
Tagset Option: flow Not recognized.
93 ods excel options(flow='tables'
94 sheet_name='Using flow option');
95
96 proc report data=sashelp.baseball;
97 column name team league division natbat nhits nruns nrbi;
98 run;

NOTE: There were 322 observations read from the data set SASHELP.BASEBALL.
NOTE: PROCEDURE REPORT used (Total process time):
real time 1.85 seconds
cpu time 1.35 seconds

I tried adding the same ods excel options you suggested (flow, row height) to the code I that has overflow for region and for some unknown reason, it didn't' help/make a difference. The only messages I got in the log was:

Tagset Option: flow Not recognized.

Tagset Option: flow Not recognized

I'm using Excel 2016 and my SAS version is 9.4 TS Level 1M2

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

SuzanneDorinski — Wed, 26 Sep 2018 20:53:36 GMT

You are using a version of SAS before the FLOW option was introduced. You have SAS 9.4M2. FLOW is available in SAS 9.4M4 or SAS 9.4M5.

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

jcis7 — Wed, 26 Sep 2018 20:56:31 GMT

Do I have to get a new license (i.e, pay for a whole nother year) to have the newer version? Or is there an update we can apply? Thanks!

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

SuzanneDorinski — Wed, 26 Sep 2018 23:16:46 GMT

I'm an end-user at my organization, so I don't know much about how the SAS license works when new versions are released.

Re: 1) variable with long length flows onto next line (but same cell in Excel) and 2) removing %

jcis7 — Mon, 01 Oct 2018 13:44:13 GMT

Thank you!