topic Re: ODS Excel - proc print columns widths in SAS Programming

ODS Excel - proc print columns widths

sasheadache — Wed, 06 Dec 2023 15:44:36 GMT

I need some further assistance on a solution detailed in a previous post please.

Solved: Re: ODS Excel - problem with wrap text - SAS Support Communities

My issue is after passing the list of length values to the macro variable, when it then applies this in the proc print via the 'absolute_column_width = "&length_var"' section it's applying the first length from the macro variable to all columns, rather than being dynamic and changing for each one.

/* 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" );

Note: I am not using 'tagsets.excel' as we only have 'tagsets.excelbase' or 'tagsets.excelxp' in our current sas installation, but I don't think this is the issue anyway.

Re: ODS Excel - proc print columns widths

PaigeMiller — Wed, 06 Dec 2023 15:53:53 GMT

What is the value of the macro variable &length_var ?

Can you show us the entire code instead of fragments?

You mention tagsets.excelxp but you are not using tagsets.excelxp, why? You are using ODS EXCEL, not tagsets.excelcp

Re: ODS Excel - proc print columns widths

ballardw — Wed, 06 Dec 2023 16:02:23 GMT

And the values of Length in data set OTHER_NAME are what?

You should be made aware that PX is not a portable unit of measure. Using such a unit becomes dependent on the display and running the code on a different system can result on unreadable text.

Re: ODS Excel - proc print columns widths

sasheadache — Thu, 07 Dec 2023 07:24:26 GMT

The full code extract was on the original post - included now for reference.

In my adapted version of this code the only difference is the fact I am using 'ods tagsets.excelbase' instead of 'ods excel' from the original post.

/* 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 ;

The value of the macro variable resolves to:

83 %put &length_var.;
SYMBOLGEN: Macro variable LENGTH_VAR resolves to 8,11,14,125,8,8,8,8,8,11,125,14,14,14
8,11,14,125,8,8,8,8,8,11,125,14,14,14

The first value in the macro variable is 8 and the proc print is then applying this length to all columns.

Thanks

Re: ODS Excel - proc print columns widths

PaigeMiller — Thu, 07 Dec 2023 12:06:22 GMT

I am not able to reproduce this.

%let length_var=8,11,14,125,8,8,8,8,8,11,125,14,14,14;
ods excel file="test.xlsx" options(flow = "header, data" absolute_column_width="&length_var" absolute_row_height = "20px");
proc print data=sashelp.cars noobs;
run;
ods excel close;

Re: ODS Excel - proc print columns widths

sasheadache — Thu, 07 Dec 2023 15:24:50 GMT

So I don't have the ability to simply use 'ods excel' because it produces excel files which are full of nonsense i.e. special characters & symbols (guessing its a SAS config issue at my company). But the good news is I have now managed to replace the same steps using my current method of output using 'ods tagsets.excelbase'.

I didn't realise until now how many different variations there were with outputting and formatting data to Excel, so that should keep me busy upskilling for a few weeks. 🙂

Thanks for taking the time to reply - all the best!