ODS Excel - problem with wrap text

[Onizuka]

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&docsetVe...) 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

[Criptic]

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

[Onizuka]

---

@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

[Criptic]

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 😉

[Onizuka]

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

[Patrick]

---

@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.

[Criptic]

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

[Onizuka]

---

@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 !

[Onizuka · Accepted solution]

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 ;

[Patrick]

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;

[Onizuka]

@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 !