Need to insert line break in Excel using PROC EXPORT

[TaniaRTP]

I am creating an Excel workbook and for a specific cell within a worksheet, I need to insert line breaks/wrapping to make it more readable.

As an example, my variable MHDEETS has this value and looks like this within Excel

01. Seasonal allergy /Ongoing 02. Appendicectomy YYYY/YYYY 03. Hypoxia YYYY-MM/Ongoing  04. Hypoxia YYYY-MM-DD/YYYY-MM-DD 05. Right ventricular failure YYYY-MM-DD/YYYY-MM-DD 06. Atrial flutter YYYY-MM-DD/YYYY-MM-DD

however, I'd like for it to appear like below:

01. Seasonal allergy /Ongoing

02. Appendicectomy YYYY/YYYY

03. Hypoxia YYYY-MM/Ongoing 

04. Hypoxia YYYY-MM-DD/YYYY-MM-DD

05. Right ventricular failure YYYY-MM-DD/YYYY-MM-DD

06. Atrial flutter YYYY-MM-DD/YYYY-MM-DD

Here is the simple SAS code

proc export data=finalae outfile="&maindir.Output\STUDYID Serious and non-serious AEs &tlfdt..xlsx" label replace dbms=xlsx ;
sheet='STUDYID_AE_MH_CM' ;
run ;

Does anyone have suggestions on how I can do this? Any help would be appreciated.

[ballardw]

What is defined length of the variable(s) that you need to do this with? Proc Contents will tell you this if you don't know.

What is the longest actual value (may not match the defined length) but need to know because if the longest current value is 40 characters and the variable has a defined length of 42 then there is no way to insert 5 characters into your existing variable without losing data (40 + 5= 45 means 3 too many characters) and you would need a new variable.

How do we know from the values in the variable(s) where the line break gets inserted? This could get pretty complicated as with all those YYYY-MM-DD that you show then a simple search for 0 or 01 is likely to not be sufficient.

[SASKiwi]

By design PROC EXPORT is for exporting to Excel with minimal formatting options. I suggest you try ODS EXCEL working with PROC REPORT as that gives you far more formatting capabilities.

However, I question your requirement to store multiple medical conditions in one cell. What if you want to report on the number of hypoxias, for example? You can't easily filter your data by condition. It would be better to create one row per condition if you want to do that type of analysis.

[Ksharp]

data x;
x='01. Seasonal allergy /Ongoing  '|| "0D0A"x ||'
02. Appendicectomy YYYY/YYYY '|| "0D0A"x ||'
03. Hypoxia YYYY-MM/Ongoing  '|| "0D0A"x ||'
04. Hypoxia YYYY-MM-DD/YYYY-MM-DD  '|| "0D0A"x ||'
05. Right ventricular failure YYYY-MM-DD/YYYY-MM-DD  '|| "0D0A"x ||'
06. Atrial flutter YYYY-MM-DD/YYYY-MM-DD';
run;
proc export data=x outfile="c:\temp\AEs.xlsx" label replace dbms=xlsx ;
sheet='STUDYID_AE_MH_CM' ;
run ;

[TaniaRTP · Accepted solution]

Thank you for your advice. I previously used '0A'x to force a break. I inserted "0D0A"x too. Neither worked with the Carriage break (also called Wrap Text) feature in Excel.

[Patrick]

Adding newline character '0A'x will only do the job if wrap text is also selected for the cell.

Proc Export is for writing data sheets where formatting is of no relevance. If you're more after a report then consider using ODS Excel together with Proc Report as this will give you a lot of formatting options. 

Here some sample code how this could look like

%let xlsx_file=c:\temp\AEs.xlsx;
%let xlsx_sheet=STUDYID_AE_MH_CM;

data have;
  col1='01. Seasonal allergy /Ongoing  ';
  col2='02. Appendicectomy YYYY/YYYY ';
  col3='03. Hypoxia YYYY-MM/Ongoing';
  col4='04. Hypoxia YYYY-MM-DD/YYYY-MM-DD';
  col5='05. Right ventricular failure YYYY-MM-DD/YYYY-MM-DD';
  col6='06. Atrial flutter YYYY-MM-DD/YYYY-MM-DD';
run;

data inter;
  set have;
  length MHDeets $4000;
  MHDeets=catx('0A'x, of col:);
  drop col:;
run;

ods excel 
  file="&xlsx_file" 
  options(
    flow = "Tables" 
    sheet_name="&xlsx_sheet"
    )
  ; 
proc report data=inter nowd wrap;
  column MHDeets;
  define MHDeets /display style(column)={vjust=center width=40em};
run;
ods excel close;

Consider to not creating this concatenated string with line feeds but to write these string on multiple lines. Not only will this avoid changing your source strings but it should also make it easier to filter your data in Excel.

In my environment: If using Proc Export for the variable with the linefeed added wrap text did not get selected on the Excel cell. The moment I've added this manually to the Excel cell the text got wrapped as desired.

[TaniaRTP]

Thank you @Patrick for your feedback and for simplifying my life with CATX function tip.

The output structure was developed & approved several years ago; one row per event. Now, I am adding a few additional columns however these columns are not the focus of the list and won't be used for filtering. The end users need to see a list of which ConMeds were taken within 30 days of the event, what terms are in the subject's Med Hx, etc.

When I saw that you were able to wrap in your environment with '0A'x, it was evident that something else was wrong with my environment or the program.

[Patrick]

@TaniaRTP  I'd still go for ODS Excel together with Proc Report as this just gives you so much more control - now and for future requirements.

Below sample code demonstrates how to just print the rows while at the same time also creating your concatenated variable MHDeets.

Thanks to @Ksharp 's answer here I also was able to impute the line breaks differently so they'll work also with other output destinations like HTML.

%let xlsx_file=c:\temp\AEs.xlsx;
%let xlsx_sheet=STUDYID_AE_MH_CM;

data have;
  other_var='Row 1';
  col1='01. Seasonal allergy /Ongoing  ';
  col2='02. Appendicectomy YYYY/YYYY ';
  col3='03. Hypoxia YYYY-MM/Ongoing';
  col4='04. Hypoxia YYYY-MM-DD/YYYY-MM-DD';
  col5='05. Right ventricular failure YYYY-MM-DD/YYYY-MM-DD';
  col6='06. Atrial flutter YYYY-MM-DD/YYYY-MM-DD';
  output;
  call missing(col2,col3,col5,col6);
  other_var='Row 2';
  output;
run;

proc sql noprint;
  select name into :var_list separated by ' '
  from dictionary.columns
  where 
    libname='WORK' 
    and memname='HAVE' 
    and prxmatch('/^col\d+/i',strip(name))>0
    order by name
  ;
quit;

ods excel 
  file="&xlsx_file" 
  options(
    flow = "Tables" 
    sheet_name="&xlsx_sheet"
    )
  ; 
ods escapechar='^';
proc report data=HAVE nowd style(header)={just=l} style(column)={vjust=center};
  column col: other_var MHDeets;
  define col: /noprint;
  define other_var  /display;
  define MHDeets    /computed style(column)={width=35em};
  compute MHDeets /character length=4000;
    array _cols{*} &var_list;
    %macro null;
    /* in case there are non-print characters that need removal */
    do _i=1 to dim(_cols);
      _cols[_i]=compress(_cols[_i],,'kw');
    end;
    %mend;
    MHDeets=catx('^n', of _cols[*]);
  endcomp;

run;
ods excel close;

Excel:

HTML when using SAS EG:

[TaniaRTP]

This should've been accepted as solution.

After seeing another user's response that wrap text also worked for them, I started reviewing. One of the last manipulations was to strip unprintable characters.