topic Re: proc report : unexpected return cariage in ODS and Base Reporting

proc report : unexpected return cariage

Nasser_DRMCP — Tue, 13 Dec 2022 13:17:37 GMT

data TEST ;
col1 = "Exécution amiable - retour huissier" ;
col2 = "Exécution amiable - retour huissier" ;
col3 = "Exécution amiable - retour huissier" ;
output ;
col1 = "Exécution amiable - retour huissier" ;
col2 = "Exécution amiable - retour huissier" ;
col3 = "Exécution amiable - retour huissier" ;
output ;
run ;



ODS LISTING CLOSE;
ODS EXCEL FILE="$GRPFPU/REQUETES_PROD/TEST_NASSER.xlsx" STYLE=HTMLBlue;
ODS Excel options (SHEET_NAME = "Base"
                   EMBEDDED_TITLES = "off"
                   FROZEN_HEADERS = "yes"
                   AUTOFILTER = "all"
                   absolute_row_height='30'
                   ROW_HEIGHTS="0,20,0,0,0,0,0");


PROC REPORT DATA=TEST NOWD
    STYLE(header)=[background=CX9BC2E6 foreground=white]
    STYLE(column)=[cellwidth=3cm ]
	;
    COLUMN
      col1 col2 col3;

RUN;
ODS Excel CLOSE;
ODS LISTING;

hello,

by executing this code, I note that there is a return cariage indisde the cell excel.

how could I prevent this ?

thanks a lot in advance for your help

kind regards

Nasser

Re: proc report : unexpected return cariage

data_null__ — Tue, 13 Dec 2022 13:48:46 GMT

You need the FLOW option.

FLOW=<"cell-names", "DATA", "HEADERS", "ROWHEADERS", "TABLES", "TEXT" >

specifies that a designated Worksheet area enables Wrap Text and disables newline character insertion. Excel wraps the text to the column width.

cell-names

enables Wrap Text for a single cell such as “A12” or a cell range such as “C1:E4”.

DATA

enables Wrap Text for table data cells.

HEADERS

enables Wrap Text in table column headers.

Alias

HEADER

ROWHEADERS

enables Wrap Text for table row headers.

Alias

ROWHEADER

TABLES

enables Wrap Text for all parts of a table: HEADER, ROWHEADER, and DATA.

Alias

TABLE

TEXT

makes ODS TEXT output work like titles, footnotes, PROC titles, and BY lines. The text is written into multiple merged cells and Wrap Text is enabled.

Re: proc report : unexpected return cariage

Nasser_DRMCP — Tue, 13 Dec 2022 14:38:34 GMT

hello

thanks for your quick respons but i did not manage to implement it;

I use the option FLOW but the text still contains a return carriage

thanks

Nasser

Re: proc report : unexpected return cariage

Nasser_DRMCP — Tue, 13 Dec 2022 14:39:09 GMT

i would like to avoid return cariage

Re: proc report : unexpected return cariage

data_null__ — Tue, 13 Dec 2022 15:35:29 GMT

I suspect you used the DEFINE statement option FLOW. You need ODS EXCEL OPTIONS(FLOW="TABLE')

ODS Excel options (SHEET_NAME = "Base"
                   EMBEDDED_TITLES = "off"
                   FROZEN_HEADERS = "yes"
                   AUTOFILTER = "all"
                   FLOW = 'Table'
                   absolute_row_height='30'
                   ROW_HEIGHTS="0,20,0,0,0,0,0");

@Nasser_DRMCP wrote:

hello

thanks for your quick respons but i did not manage to implement it;

I use the option FLOW but the text still contains a return carriage

thanks

Nasser

Re: proc report : unexpected return cariage

Nasser_DRMCP — Tue, 13 Dec 2022 15:48:23 GMT

thanks a lot

I get the attended result in excel by cliking on button "automatic retour cariage"

Why this button is turn on ?

thanks

Re: proc report : unexpected return cariage

Nasser_DRMCP — Tue, 13 Dec 2022 16:00:48 GMT

I wonder why this button is activated;

Re: proc report : unexpected return cariage

Nasser_DRMCP — Wed, 14 Dec 2022 09:23:56 GMT

hello

my problem is that wrap text button is enabled by opening my excel file. i would like it to be disabled

thanks a lot in advance for your help

Nass

Re: proc report : unexpected return cariage

Ksharp — Wed, 14 Dec 2022 11:42:31 GMT

PROC REPORT DATA=TEST NOWD
    STYLE(header)=[background=CX9BC2E6 foreground=white]
    STYLE(column)=[cellwidth=3cm ]
 ;
    COLUMN
      col1 col2 col3;
define _all_/style={tagattr='wrap:no'};
RUN;

Re: proc report : unexpected return cariage

Nasser_DRMCP — Wed, 14 Dec 2022 12:49:03 GMT

thanks a lot for your quick respons

the "wrap text" button is disabled and the text has not return carriage

But I wonder why there is a return carriage inside the formul bar ?

Re: proc report : unexpected return cariage

Nasser_DRMCP — Wed, 14 Dec 2022 13:08:29 GMT

by copy/past the content of the excel sheet into ultra edit I can note that the text contains character return cariage; and I do't know why

Re: proc report : unexpected return cariage

Ksharp — Thu, 15 Dec 2022 11:38:06 GMT

That is because you used this style "cellwidth=3cm". if remove it ,you would not got return cariage .

PROC REPORT DATA=TEST NOWD
    STYLE(header)=[background=CX9BC2E6 foreground=white]

    STYLE(column)=[cellwidth=3cm ]  /*<-----  Remove it */
 ;
    COLUMN
      col1 col2 col3;
define _all_/style={tagattr='wrap:no'};
RUN;

Re: proc report : unexpected return cariage

Nasser_DRMCP — Thu, 15 Dec 2022 12:57:43 GMT

thanks for your quick respons

by doing that, tthe result is just below. and I agree, the carriage return no longer appaers.

but the column is width.

what I would le to get is that below

many thanks for your help

Re: proc report : unexpected return cariage

Ksharp — Fri, 16 Dec 2022 12:00:21 GMT

OK. Try this option "Absolute_Column_Width".

ODS LISTING CLOSE;
ODS EXCEL FILE="c:\temp\TEST_NASSER.xlsx" STYLE=HTMLBlue;
ODS Excel options (SHEET_NAME = "Base"
                   EMBEDDED_TITLES = "off"
                   FROZEN_HEADERS = "yes"
                   AUTOFILTER = "all" 
                   absolute_row_height='30'   Absolute_Column_Width="10,10,10"   /*<---Try this one*/
                   ROW_HEIGHTS="0,20,0,0,0,0,0");


PROC REPORT DATA=TEST NOWD
    STYLE(header)=[background=CX9BC2E6 foreground=white]

 ;
    COLUMN
      col1 col2 col3;
define _all_/style={tagattr='wrap:no' } ;
RUN;
ODS Excel CLOSE;
ODS LISTING;

Re: proc report : unexpected return cariage

Nasser_DRMCP — Fri, 16 Dec 2022 13:08:42 GMT

hello Ksharp

it works very well as attended !

thanks for your efficiency