topic Re: How to change the output format of a few columns in ods excel? in ODS and Base Reporting

How to change the output format of a few columns in ods excel?

vgoel — Tue, 07 Sep 2021 19:59:50 GMT

Hi, I am generating some excel files out of a dataset and I want all the columns in the output excel to be of string/text type with a few exceptions for which I want to decide the format. Also I want to preserve the order of the columns in the dataset.

Currently I am using this piece of code on the sashelp.cars dataset:

options symbolgen;

data output_x;
	set sashelp.cars;
run;

/*define max no. rows in the output*/
%let num_of_rows=150;
%let n=&num_of_rows;

proc sql noprint;
	select count(*) into: nobs from output_x;
quit;

/*define the no. of parts the files needs to divided in*/
%let tot=%sysfunc(ceil(%eval((&nobs / &n)+1)));

%macro test;
	/*loop through one part at a time*/
	%do j=1 %to &tot;
		%let fobs=%eval((&num_of_rows*%eval(&j-1))+1);
		%let lobs=%eval(&num_of_rows*&j);

		data output_xlsd&j;
			set output_x(FIRSTOBS=&fobs. OBS=&lobs.);
		run;

		ods excel file="/location/&j._of_&tot..xlsx";

		proc print data=output_xlsd&j style(data)={tagattr='type:text'};
		run;

		ods excel close;
	%end;
%mend;

%test;

This converts all the columns to text. But I'd want to give some of the columns a number format, for instance EngineSize should be a number with 2 decimal places; rest all as text/string.

In real scenario I would have 30 columns which I want to give a number format some whole numbers some with two decimal places. And the rest (about 400) to be as text/string in the excel output.

How can I achieve this?

Re: How to change the output format of a few columns in ods excel?

data_null__ — Tue, 07 Sep 2021 20:35:07 GMT

You can use PROC TRANSPOSE to convert numeric variables to character using the associated formats. This process can preserve variable order and variable

labels.

data cars;
	set sashelp.cars;
   _obs_+1;
   run;
proc transpose data=cars out=cars1;
   by _obs_;
   var make--length;
   format enginesize 8.2;
   run;
data cars2;
   set cars1;
   col1 = left(col1);
   run;
proc transpose data=cars2 out=charcars(drop=_name_ _obs_);
   by _obs_;
   var col1;
   id _name_;
   idlabel _label_;
   run;
proc contents varnum;
proc print;
   run;

Re: How to change the output format of a few columns in ods excel?

Reeza — Tue, 07 Sep 2021 23:05:37 GMT

I think you may need to use PROC REPORT and to define each column individually....definitely using a macro if you have 400 though you may be able to use a similar data approach as with PROC PRINT and then only override the few columns you need.

Re: How to change the output format of a few columns in ods excel?

vgoel — Wed, 08 Sep 2021 06:25:40 GMT

To give you the full background, initially the process involved ods html and we were creating .xls file that were not actually Excel, but MS Excel was able to open them and we had to do a Save As Excel. This caused a few problems one being the size of those html files.

We were actually using PROC REPORT with ods html:

proc report data = output_xlsd&j;
	column _all_;
	title;
	define EngineSize	 / display style(column)={htmlstyle="mso-number-format:0\.00"};
run;

This would only convert the EngineSize to numeric type while all the others were text by default in the output file.

So now I am planning to change that piece of code to use ods excel to create true excel files. However the problem is that with ods excel it does not change them to text/string. And I believe I'd have to explicitly define the formats for all the variables(which would be 400). I am looking for a way around this.

Re: How to change the output format of a few columns in ods excel?

Reeza — Wed, 08 Sep 2021 15:21:50 GMT

Have you tried this yet or are you assuming you're having these issuees?