topic ODS Excel - problem with wrap text in SAS Programming

ODS Excel - problem with wrap text

Onizuka — Wed, 24 Jul 2019 08:06:47 GMT

Hello,

I'm using the ODS excel to export some data from SAS to Excel. I have a "problem" which is not so terrible but a little contraignant :

it always wrap the text...

I looked the documentation (https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVersion=9.4&locale=en) and tryied to use the instruction FLOW which is not working...

Is there a way to "delete" the wrapping text without necessarily having to define the length of all the cells ?

Many thanks..

Onizuka

Re: ODS Excel - problem with wrap text

Criptic — Wed, 24 Jul 2019 09:48:48 GMT

Hey @Onizuka,

you could use the option absolute_column_width='20' to set the width for all columns to a certain width.

Looks kind of strange too me but that is the beauty of ods.

Kind Regards

Re: ODS Excel - problem with wrap text

Onizuka — Wed, 24 Jul 2019 09:54:28 GMT

@Criptic wrote:
Hey @Onizuka,

you could use the option absolute_column_width='20' to set the width for all columns to a certain width.

Looks kind of strange too me but that is the beauty of ods.

Kind Regards

Hi @Criptic ,

Thanks for your reply ! The problem using width = 20 is that is set all columns, but i have some columns which needs 40 characters, others 3 etc..

So, ods is very pretty but sometimes it sucks !! haha

Re: ODS Excel - problem with wrap text

Criptic — Wed, 24 Jul 2019 09:58:22 GMT

In that case you could comma seperate the numbers of the cell - I guess you could via proc contens get the value of the column name count the length and then create a macro-variable which contains all these number seperated by commas and plug that into the ods statement - seems like a lot of trouble put hey - if there is a will there is a way 😉

Re: ODS Excel - problem with wrap text

Onizuka — Wed, 24 Jul 2019 10:57:00 GMT

I have already try to do this, the problem is (and I don't know why) that when i use a proc contents on my table, the order of columns is not the same than the real table ! wtf haha

Re: ODS Excel - problem with wrap text

Patrick — Wed, 24 Jul 2019 11:03:09 GMT

@Onizuka wrote:

I have already try to do this, the problem is (and I don't know why) that when i use a proc contents on my table, the order of columns is not the same than the real table ! wtf haha

May be using order=varnum in proc contents will give you the same order.

Re: ODS Excel - problem with wrap text

Criptic — Wed, 24 Jul 2019 11:11:54 GMT

Ordering by varnum is the way to go here, because for some strange reason proc contents orders alphabetically by default

Re: ODS Excel - problem with wrap text

Onizuka — Wed, 24 Jul 2019 12:29:42 GMT

@Criptic wrote:
Ordering by varnum is the way to go here, because for some strange reason proc contents orders alphabetically by default

Ah, yes indeed it is possible like that haha

I'm testing, I hope the wrapping text will stop but i don't think so !! I'm coming later to tell if it works or not !

Re: ODS Excel - problem with wrap text

Onizuka — Wed, 24 Jul 2019 13:09:33 GMT

Okey, it seems to work ! Thanks @Criptic and @Patrick for your answers 🙂

For all people that have the same problem, this is the steps I have used :

/* 1) proc contents to recover the length of each variables */
proc contents data = table_you_want_export 
out = other_name (keep = varnum length) noprint ; 
run ;

/* sorting by varnum to have the same order that the table you want export */
proc sort data = other_name ; 
by varnum ; 
run ;
	
/* creating a macro variable containing these lengths separated by a comma */
proc sql noprint ; 
select length into : length_var separated by ',' 
from other_name ; 
quit ;

/* EXPORT */

ods excel file="path\name_output.xlsx" 
options(
flow = "header, data" 
absolute_column_width = "&length_var" 
absolute_row_height = "20px"
); 

Proc print data = table_you_want_export noobs ;
run ;

ods excel close ;

Re: ODS Excel - problem with wrap text

Patrick — Wed, 24 Jul 2019 16:12:08 GMT

If you use the Proc Contents option ORDER=VARNUM then you don't need the sort step.

proc contents data = table_you_want_export 
  out = other_name (keep = varnum length) 
  noprint 
  order=varnum
  ;
run;

You could also get all the information directly from dictionary.columns

proc sql noprint;
  select length into : length_var separated by ',' 
  from dictionary.columns
  where libname='<LIBREF OF TABLE_YOU_WANT_EXPORT in uppercase>' 
        and memname='<NAME OF TABLE_YOU_WANT_EXPORT in uppercase>'
  order by varnum
  ;
quit;

Re: ODS Excel - problem with wrap text

Onizuka — Thu, 25 Jul 2019 07:23:32 GMT

@Patrick,

Thanks for you answer, the "order = varnum" in the proc contents doesn't work for me, it doesn't sort the table !

But the second program you made is perfect ! Thanks 🙂

I don't have the reflex to go through metadatas !