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:

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

Hello crazy SAS world

Welcome to my humble SAS blog. During my years of SAS programming in the pharmaceutical industry I had to come up with solving seemingly impossible tasks again and again. Although SAS does not offer a concise and logical way to solve problems, almost every problem can be solved somehow. This often involves using horrible hacks which make any programmer of a modern programming language want to cringe. Nevertheless, I quite enjoy this way of programming not least for having a good laugh once in a while.