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 ?
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 ;
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
@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
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 😉
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
@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 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 !
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 ;
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;
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 !
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.