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

[Daily1]

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

[andreas_lds]

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.

[Kathryn_SAS]

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;

[Tom]

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?

[Quentin]

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;

[Daily1]

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

[andreas_lds]

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

[ballardw]

---

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

[Daily1]

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

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.

[andreas_lds]

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. 

[Ksharp]

Yes.

Check PROC ODSLIST and PROC ODSTEXT :