topic Re: Concatenate with Carriage Return and special formatting in SAS Programming

Concatenate with Carriage Return and special formatting

Lost_Gary — Tue, 05 Jun 2018 13:10:43 GMT

OK, I have a special situation where I am trying to combine 2 fields into a single field with a carriage return and the first line of the output to be bold. For example:

Mary Smith

123 Main St.

Anytown USA

I am trying to put this output directly to Excel and I have been attempting ODS to make this happen mainly because of the bold function that I need for part of the field. Can this happen directly in ODS? or is there a way to concatenate these with a carriage return, then bold only a portion of the field?

Thank you all.

Re: Concatenate with Carriage Return and special formatting

Ksharp — Tue, 05 Jun 2018 13:32:12 GMT

Use escapechar= and special character '~n' .

ods escapechar='~' ;
...........
x='Mary Smith ~n 123 Main St. ~n Anytown USA ~n';

Re: Concatenate with Carriage Return and special formatting

Lost_Gary — Tue, 05 Jun 2018 14:03:57 GMT

Not sure I completely understand how to use the escapechar feature. Is this used in the dataset or in the proc print of the ods output?

ods escapechar='~';

data new_data;

set old_data;

name_address = name ~n address ~n;

run;

ods escapechar='~';

proc print data old_data;

var x=name ~n address ~n;

run;

I don't seem to be having any luck with trying these methods. I must be missing something.

Re: Concatenate with Carriage Return and special formatting

Lost_Gary — Tue, 05 Jun 2018 16:46:13 GMT

maybe if i wasn't calling it a carriage return I would have found this post (carriage return - how old am I?):

https://communities.sas.com/t5/Base-SAS-Programming/Adding-a-line-feed-character-when-joining-two-columns-into-one/td-p/69419

so, catx('0a'x,Name,Address) is just about what I was looking for, but How do I bold just the name field as part of this new combined field? Is that possible?

Re: Concatenate with Carriage Return and special formatting

ballardw — Tue, 05 Jun 2018 17:06:56 GMT

Data set values do not contain any characteristics such as font face, weight, bold, underline or such.

Style attributes can be applied to reporting procedures such as Proc Print, Tabulate, Report or the results of the data step Report Writing Interface.

Or insert raw codes for the destination you are going to use such that the appropriate file viewer will apply the characteristic you want.

If you are looking to mix font weight in a single cell of a table perhaps the RWI approach would be best but I can't provide any specific examples other than are in the online documentation.

Re: Concatenate with Carriage Return and special formatting

BrunoMueller — Tue, 05 Jun 2018 19:41:43 GMT

You can use the ODS Inline formatting functions to do this. Unfortunately the ODS EXCEL destination does not seem to honor this, please contact technical support. See the code below for an example. It works allright for PDF and HTML and tagsets.excelxp.

ods escapechar="~";
data reportData;  
  set sashelp.class;
  length newColumn newcolum2 $ 64;

  newColumn = cats(
    "~{style [font_weight=bold]", name, "}"
    , "~{newline}"
    , catx(" ", age, sex)
  );

  newColum2 = catx("~{newline}", name, sex, age);
run;

ods excel file="c:\temp\sample.xlsx";
ods pdf file="c:\temp\sample.pdf";
ods tagsets.excelxp file="c:\temp\sample.xml";
proc print data=reportData;
run;
ods tagsets.excelxp close;
ods pdf close;
ods excel close;

Re: Concatenate with Carriage Return and special formatting

Lost_Gary — Thu, 28 Jun 2018 18:17:24 GMT

My final code is :

new_var = catx("~{' ', style [font_weight=bold]", old_var1, '0a'x, "}"

, catx('0a'x,"~{style [font_weight=bold]", old_var2, "}"

, catx('0a'x, " ", old_var3)));

This formatting works within ODS Excel output that I needed.

Thank you all for your help.