topic Re: Using text wrapping and multiple lines without carriage return - ODS EXPORT and Proc Report in ODS and Base Reporting

Using text wrapping and multiple lines without carriage return - ODS EXPORT and Proc Report

djrisks — Mon, 11 Oct 2021 21:19:55 GMT

Hello,

I've outputted an Excel file using SAS. This is the first image below. I will like the highlighted text in yellow to still wrap and be on multiple lines (for better viewing), but not add an carriage return. The yellow highlighted text in the second image shows what I would like, such as the text is wrapped and on multiple lines, and when you look in the top menu, you can see that there is no carriage return in the text. But, the second image is creating in Excel. Do you have any ideas how I can do this in SAS please? The proc report code that I have been using is below too, and the dataset that is being used has been attached.

ods excel file="&export" ods excel options(flow="tables" sheet_name="QSORRES Mapping" absolute_column_width = "22, 23, 16, 60, 60, 13, 60, 65");
ods escapechar='~';
proc report data=input_data nowd spanrows wrap style(header)={textalign=left /*fontsize=10pt fontweight=bold*/ /*fontfamily="Albany AMT"*/} style(column)={textalign=left /*fontsize=10pt  *//*fontfamily="Albany AMT"*/};
     column ('~S={foreground=black}Raw'  FORMNAME_TEST FORMNAME_PRODUCTION ITEMNAME VALUE)  
            ('~S={foreground=black}SDTM' QSORRES QSSTRESN QSSTRESC Comments_Special_characters_);
     define FORMNAME_TEST / "FormName Test" display style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define FORMNAME_PRODUCTION / "FormName Production" style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define ITEMNAME / "ItemName" style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define VALUE / "Value" style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=#E2EFDA foreground=black};

     define QSORRES / style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=D9E1F2} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESN / format=8. style(column)={vjust=c} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESC / style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=#D9E1F2 foreground=black};
     define Comments_Special_characters_/ "Comments (Special characters)" style(column)={vjust=c} style(header)={background=#D9E1F2 foreground=black};     
run;

ods excel close;

Thank you!

Re: Using text wrapping and multiple lines without carriage return - ODS EXPORT and Proc Report

djrisks — Mon, 11 Oct 2021 21:36:27 GMT

Actually, this is the code:

ods excel file="&export"  options(flow="tables" sheet_name="QSORRES Mapping" absolute_column_width = "22, 23, 16, 60, 60, 13, 60, 65");
ods escapechar='~';
proc report data=input_data nowd spanrows wrap style(header)={textalign=left /*fontsize=10pt fontweight=bold*/ /*fontfamily="Albany AMT"*/} style(column)={textalign=left /*fontsize=10pt  *//*fontfamily="Albany AMT"*/};
     column ('~S={foreground=black}Raw'  FORMNAME_TEST FORMNAME_PRODUCTION ITEMNAME VALUE)  
            ('~S={foreground=black}SDTM' QSORRES QSSTRESN QSSTRESC Comments_Special_characters_);
     define FORMNAME_TEST / "FormName Test" display style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define FORMNAME_PRODUCTION / "FormName Production" style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define ITEMNAME / "ItemName" style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define VALUE / "Value" style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=#E2EFDA foreground=black};

     define QSORRES / style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=D9E1F2} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESN / format=8. style(column)={vjust=c} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESC / style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=#D9E1F2 foreground=black};
     define Comments_Special_characters_/ "Comments (Special characters)" style(column)={vjust=c} style(header)={background=#D9E1F2 foreground=black};     
run;

ods excel close;

Re: Using text wrapping and multiple lines without carriage return - ODS EXPORT and Proc Report

Patrick — Tue, 12 Oct 2021 06:33:47 GMT

Below seems to get the wrapping right.

ods excel 
  file="&export"  
  options(flow="tables" sheet_name="QSORRES Mapping");
ods escapechar='~';
proc report data=input_data nowd spanrows wrap style(header)={textalign=left /*fontsize=10pt fontweight=bold*/ /*fontfamily="Albany AMT"*/} style(column)={textalign=left /*fontsize=10pt  *//*fontfamily="Albany AMT"*/};
     column ('~S={foreground=black}Raw'  FORMNAME_TEST FORMNAME_PRODUCTION ITEMNAME VALUE)  
            ('~S={foreground=black}SDTM' QSORRES QSSTRESN QSSTRESC Comments_Special_characters_);
     define FORMNAME_TEST / "FormName Test" display style(column)={vjust=c width=22em} style(header)={background=#E2EFDA foreground=black};
     define FORMNAME_PRODUCTION / "FormName Production" style(column)={vjust=c width=23em} style(header)={background=#E2EFDA foreground=black};
     define ITEMNAME / "ItemName" style(column)={vjust=c width=10em} style(header)={background=#E2EFDA foreground=black};
     define VALUE / "Value" style(column)={vjust=c width=16em} style(header)={background=#E2EFDA foreground=black};

     define QSORRES / style(column)={vjust=c tagattr='wraptext:yes' width=60em} style(header)={background=D9E1F2} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESN / format=8. style(column)={vjust=c width=13em} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESC / style(column)={vjust=c width=10em} style(header)={background=#D9E1F2 foreground=black};
     define Comments_Special_characters_/ "Comments (Special characters)" style(column)={vjust=c width=65em} style(header)={background=#D9E1F2 foreground=black};     
run;

ods excel close;

Re: Using text wrapping and multiple lines without carriage return - ODS EXPORT and Proc Report

djrisks — Tue, 12 Oct 2021 14:00:41 GMT

Thank you for your response. Unfortunately, I still get the issue with the values being squashed and not immediately appearing in multiple lines within Excel. Please see the example below. Do you know how to overcome this?

Many thanks,

Kriss

Re: Using text wrapping and multiple lines without carriage return - ODS EXPORT and Proc Report

Patrick — Tue, 12 Oct 2021 22:30:24 GMT

The code I've posted works for me. Below how the Excel looks like in my environment.