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?
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;
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.
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.