Hacking Excel outputs Part 1 – Word wrapping

I often generate Excel outputs where individual cells contain several lines with concatenated data. This could be e.g. an adverse event listing showing a patient’s information, with a cell containing all of the patient’s adverse events (separated by carriage returns). These outputs I generate by first concatenating data into one field and separating the individual values by ‘
’. The field ADVERSE_EVENT could then e.g. be ‘Fever
Headache’. Outputting such a dataset into Excel using “ods tagsets.excelxp” will generate an Excel file in XML format with correct line breaks between the two adverse events Fever and Headache. There is however no text wrapping which has to be manually set later. There is unfortunately also no option for wrapping in tagsets.excelxp (SAS 9.2), which led me to the following method of achieving this:

1
2
3
4
5
6
7
8
9
10
11
data Reformat(keep=col2);
infile "&destination" length=l lrecl=5000;
length col2 $5000.;
input col1 $varying5000. l;
col2 = tranwrd(trim(left(col1)), '<Style ss:ID="unknown_m1_1">', '<Style ss:ID="unknown_m1_1"><Alignment ss:WrapText="1" ss:Vertical="Top" ss:Horizontal="Left"/>');
run;
data _null_ ;
set Reformat;
file "&destination" lrecl=5000;
put col2;
run ;

By setting &destination to the Excel file generated previously these two datastep first read in the Excel file as text, replace an XML tag by another one and then writing the file back as text.
The result is an Excel file with word wrapping.

Donate Bitcoins

Leave a Reply

Your email address will not be published. Required fields are marked *