topic Re: PROC ODSTEXT excel text wrapping in SAS Programming

PROC ODSTEXT excel text wrapping

SMarieH1521 — Mon, 24 Jun 2024 17:18:43 GMT

I am creating a workbook and the first page has directions that I've created using PROC ODSTEXT. Unfortunately, no matter what option I use the text continues to wrap. Can someone please tell me how to stop the text wrapping? I've tried FLOW, setting the column width, tagattr etc...

ODS EXCEL FILE = "filedestination.xlsx" OPTIONS (TITLE_FOOTNOTE_NOBREAK='YES' SHEET_NAME= "INSTRUCTIONS" EMBEDDED_TITLES='YES' Absolute_column_width = '175pt' absolute_row_height = "20pt" FLOW='DATA, TABLES, TEXT');

TITLE;

PROC ODSTEXT;

P '1. REVIEW THE ITEM TO RECONCILE TAB IN THE WORKBOOK PROVIDED ON XXX WEBSITE.'/style={tagattr='WrapText:0' fontsize=12pt};

P ' ';

P '2. REVIEW COLUMN X TO IDENTIFY ANY ITEMS DEEMED "LEAKAGE" PER DETERMINED LOGIC.' /style={tagattr='WrapText:0' fontsize=12pt};

P ' ';

P '3. FOR ANY SERVICES YOU FEEL SHOULD NOT BE INCLUDED, PLEASE NOTE THAT IN THE SPREADSHEET. THOSE SERVICES WILL BE REVIEWED BY A MEMBER OF OUR CLINICAL STAFF.'/style={tagattr='WrapText:0' fontsize=12pt};

RUN;

ods excel close;

SAS Base 9.4 TS Level 1M6

Thanks!

Re: PROC ODSTEXT excel text wrapping

data_null__ — Mon, 24 Jun 2024 22:23:10 GMT

Can you show example what you want? Maybe make it by hand and show a picture. Most folks don't like to open XLSX from external source.

Re: PROC ODSTEXT excel text wrapping

Ksharp — Tue, 25 Jun 2024 01:28:54 GMT

Using style WRAP:NO instead of wraptext:0


ODS EXCEL FILE = "c:\temp\filedestination.xlsx" 
OPTIONS (TITLE_FOOTNOTE_NOBREAK='YES' 
SHEET_NAME= "INSTRUCTIONS" EMBEDDED_TITLES='YES'
Absolute_column_width = '175pt' 
absolute_row_height = "20pt" 
FLOW='DATA, TABLES, TEXT');

TITLE; 

PROC ODSTEXT;

 P '1. REVIEW THE ITEM TO RECONCILE TAB IN THE WORKBOOK PROVIDED ON XXX WEBSITE.'/style={tagattr='Wrap:no' fontsize=12pt};

 P ' ';

 P '2. REVIEW COLUMN X TO IDENTIFY ANY ITEMS DEEMED "LEAKAGE" PER DETERMINED LOGIC.' /style={tagattr='Wrap:no' fontsize=12pt};

 P ' ';

 P '3. FOR ANY SERVICES YOU FEEL SHOULD NOT BE INCLUDED, PLEASE NOTE THAT IN THE SPREADSHEET. THOSE SERVICES WILL BE REVIEWED BY A MEMBER OF OUR CLINICAL STAFF.'/style={tagattr='Wrap:no' fontsize=12pt};


 RUN;

 ods excel close;

Re: PROC ODSTEXT excel text wrapping

Cynthia_sas — Tue, 25 Jun 2024 01:37:25 GMT

Hi:

I typically like to use PROC REPORT for title sheets or instruction sheets like this, especially when I need to make sure that the cellwidth is respected. I still used the TAGATTR, but I find cellwidth and height just seem to work better for me with PROC REPORT. I generated this output:

Using this code:

data instructions;
  length instructions $200;
  lineno = 1;
  instructions = '1. REVIEW THE ITEM TO RECONCILE TAB IN THE WORKBOOK PROVIDED ON XXX WEBSITE.';
  output;
  lineno = 2;
  instructions = ' ';
  output;
  lineno = 3;
  instructions =  '2. REVIEW COLUMN X TO IDENTIFY ANY ITEMS DEEMED "LEAKAGE" PER DETERMINED LOGIC.';
  output;
  lineno = 4;
  instructions=' ';
  output;
  lineno=5;
  instructions='3. FOR ANY SERVICES YOU FEEL SHOULD NOT BE INCLUDED, PLEASE NOTE THAT IN THE SPREADSHEET. THOSE SERVICES WILL BE REVIEWED BY A MEMBER OF OUR CLINICAL STAFF.';
  output;
 RUN;

title; footnote;
ODS EXCEL FILE = "c:\temp\instructions.xlsx" 
   OPTIONS (SHEET_NAME= "INSTRUCTIONS" orientation='landscape');

PROC report data=instructions noheader;
  column lineno instructions;
  define lineno/ order noprint;
  define instructions / display 
         style(column)={height-.5in cellwidth=10in fontsize=12pt cellpadding=7px tagattr='WrapText:0'};
 RUN;

 ods excel close;

All measurements like 10in or .5 in are converted by Excel into some unit of measure that seems opaque to me. However, I generally just fiddle around until I find a cellwidth that works for my text. I started at 8in and ended at 10. I fiddled with cellpadding, I probably didn't need it. The ability to generate PROC REPORT output without column headers using the NOHEADER option really helps with an instructions page like this.

Cynthia

Re: PROC ODSTEXT excel text wrapping

SMarieH1521 — Tue, 25 Jun 2024 17:19:56 GMT

Thank you, Cynthia! That worked!

Re: PROC ODSTEXT excel text wrapping

SMarieH1521 — Tue, 25 Jun 2024 17:26:27 GMT

KSharp, this solution worked too! Thank you!