topic Re: How to output macro variable value in RTF using ODS TEXT in SAS? in SAS Enterprise Guide

How to output macro variable value in RTF using ODS TEXT in SAS?

Daily1 — Tue, 29 Jul 2025 09:53:36 GMT

I want to read a POLICY Description from a dataset into a macro variable and then print it into an RTF file using ODS TEXT, formatted with Times New Roman and 11pt font size.
Code I Tried

proc sql noprint;
    select POLICY into :POLICY from sample_data;
quit;

ods text="^S={font_face='Times New Roman' font_size=11pt just=left} &POLICY";

The code runs but I don't see the correct formatting or output in the RTF file. Am I missing something in the ODS RTF setup?
PFA
Climate Change.docx is i want output

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

andreas_lds — Tue, 29 Jul 2025 10:47:32 GMT

Posting data as excel-file is not helpful, please post the data as data step using datalines or cards.

If the sample_data contains multiple observations, only the value of the first obs will be in &policy.

You need

ods escapechar= "^";

before using ods text.

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

Kathryn_SAS — Tue, 29 Jul 2025 12:55:16 GMT

If you are using PROC IMPORT to import the Excel file, you will need to use a UTF-8 encoded session of SAS. This will preserve the special symbols, but it will not preserve the line breaks. The following worked for me as an example, and then I just added line breaks in the Word file as desired. An alternative would be to hard-code the text using your own ODS TEXT= statements or PROC ODSTEXT.

proc import datafile='your_path\sample data.xlsx'
 out=sample_data dbms=xlsx replace;
run;

/* may need to remove embedded quotes to not conflict with resolving macro variable in double quotes */
data sample_data;
set sample_data;
policy=compress(compress(policy,'"'),"'");
run;

proc sql noprint;
    select POLICY into :POLICY from sample_data;
quit;

%put &policy;

ods listing close;
ods escapechar='^';
ods rtf file='your_path\test.rtf';

ods text="^S={font_face='Times New Roman' font_size=11pt just=left} &POLICY";

proc print data=sashelp.class;
run;

ods rtf close;
ods listing;

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

Tom — Tue, 29 Jul 2025 16:23:27 GMT

I don't understand.

Reading the DATA from a cell in an XLSX worksheet will not include any formatting that might be applied to the cell by EXCEL. So what formatting are you talking about?

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

Quentin — Tue, 29 Jul 2025 17:27:02 GMT

Can you make an example that we can run, which doesn't require downloading an Excel file?

Stealing from Kathryn's code, a small example could be:

%let policy= ; *Fill me in with some value like you have in Excel;

ods listing close;
ods escapechar='^';
ods rtf file='your_path\test.rtf';

ods text="^S={font_face='Times New Roman' font_size=11pt just=left} &POLICY";

proc print data=sashelp.class;
run;

ods rtf close;
ods listing;

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

Daily1 — Wed, 30 Jul 2025 06:01:03 GMT

I would like to represent this type of database data entry record in my document exactly as it appears

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

andreas_lds — Wed, 30 Jul 2025 06:53:19 GMT

Afaik this won't work with ods text. Try proc odstext instead.

Docs: https://documentation.sas.com/doc/de/pgmsascdc/9.4_3.5/odsproc/p01vqfrzpxj3bgn1x9elf98takez.htm

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

ballardw — Wed, 30 Jul 2025 07:21:29 GMT

@Daily1 wrote:

I would like to represent this type of database data entry record in my document exactly as it appears

Complete with bad grammar, words split across lines (it is normally considered very bad form to have short words like "the" or"and" split and you have both).

What manipulation is expected to be done to the "data" set that contains this stuff? If none, perhaps SAS shouldn't be involved at all.

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

Daily1 — Wed, 30 Jul 2025 08:33:47 GMT

https://communities.sas.com/t5/SAS-Enterprise-Guide/How-to-output-macro-variable-value-in-RTF-using-ODS-TEXT-in-SAS/td-p/971612

This is just sample data; the grammar may not be correct, and I’m unable to share the original data entry records due to confidentiality. However, the formatting and structure are similar to my actual data.

I need to create a Word document using this data format to share with my client and users. The client wants the data and formatting to be the same as the original.

I have created a stored procedure to generate this document dynamically for the client and users, but the output is not properly formatted and does not match the data exactly.

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

andreas_lds — Thu, 31 Jul 2025 04:57:54 GMT

If you have to create a report with complex formatting, have a look at the Report Writing Interface. Most layouts can be created, but this takes time.

Re: How to output macro variable value in RTF using ODS TEXT in SAS?

Ksharp — Thu, 31 Jul 2025 06:06:53 GMT

Yes.

Check PROC ODSLIST and PROC ODSTEXT :