BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lost_Gary
Obsidian | Level 7

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.  

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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;

 

View solution in original post

6 REPLIES 6
Ksharp
Super User

Use escapechar= and special character  '~n' .

 

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

Lost_Gary
Obsidian | Level 7

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;

 

or

 

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.  

Lost_Gary
Obsidian | Level 7

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-co...

 

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?  

ballardw
Super User

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.

BrunoMueller
SAS Super FREQ

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;

 

Lost_Gary
Obsidian | Level 7

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.  

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6917 views
  • 4 likes
  • 4 in conversation