topic Re: Expanind titles -not to wrap when the column width has been set in ODS and Base Reporting

Expanind titles -not to wrap when the column width has been set

LNM — Mon, 10 Jun 2019 20:11:57 GMT

I have set my namefield column width. However the namefield also contains titles for each department. I don't want the department titles to wrap because there's no data in the next column when I print the department titles. I have assigned a 1 to the rows that I don't want to wrap.

My codes is attached.

Re: Expanind titles -not to wrap when the column width has been set

heffo — Mon, 10 Jun 2019 21:27:33 GMT

It's tricky to say what it is without some data. But my first question would be if all the rows have the value 1 in rwidth? Because the width of the column can't (as far as I know) be different on different rows. And if it is so, then maybe it is better to find out if you are going to have a broad column or not, before you do the proc report.

That is, query the table to see if there is a 1 in that column then put this flag in a macro variable and work from there.

Also, put the code directly in the post instead of in an attachment. It makes it easier to copy and easier to go back to review the code.

Re: Expanind titles -not to wrap when the column width has been set

ballardw — Mon, 10 Jun 2019 23:14:31 GMT

To prevent wrapping I suspect you will need to actually truncate the length of the displayed value to a specific number of characters which would depend on the size and font used for display.

Likely a compute block such as

compute variable;

if length(variable) ge 15 then variable=substr(variable,1,15);

endcomp;

or similar.

Re: Expanind titles -not to wrap when the column width has been set

LNM — Tue, 11 Jun 2019 13:51:15 GMT

Here's what I coded. Can you help me with the syntax? Please.

compute rwidth;
if rwidth eq 1 then call define(_row_,style(column)={cellwidth=4.5 in just=l});

endcomp;

Thank you.

Re: Expanind titles -not to wrap when the column width has been set

LNM — Tue, 11 Jun 2019 13:52:16 GMT

Here's what I am try to code. Can you help me with the syntax? Please.

Here's what I coded. Can you help me with the syntax? Please.

compute rwidth;
if rwidth eq 1 then call define(_row_,style(column)={cellwidth=4.5 in just=l});

endcomp;

Thank you.

LNM

Re: Expanind titles -not to wrap when the column width has been set

Cynthia_sas — Tue, 11 Jun 2019 14:17:36 GMT

Hi:
Yes, @heffo is correct, the cell for NAME (for example), has to be the SAME on every row in a report table. You cannot have the cell for NAME be 3 inches on one row and have it be 2 inches on other rows.

The syntax you show for your CALL DEFINE with RWIDTH is incorrect, and, as I indicated, not possible to do.

Without data, no one can run your code. So it's hard to comment further. However, rather than pump the long description into the NAME field, it might be better to make a separate variable such as I show using the DESC variable that you can use like a header with a LINE statement. The LINE statement spans the whole table when it writes text, it is not limited to the width of one cell. So, here's one approach:

data class;
  length desc $100;
  set sashelp.class;
  if sex = 'F' then desc = 'Female students in the STEM Program';
  else desc='Male students in the STEM Program';
run;
  
proc report data=class;
  column sex desc name age height weight;
  define sex / order noprint;
  define desc / order noprint;
  define name / display;
  define age / display;
  define height / display;
  define weight / display;
  compute after desc;
    line ' ';
  endcomp;
  compute before desc / style={font_weight=bold just=l};
    line desc $varying100.;
  endcomp;
run;

But, if you insist on putting the long string into the NAME field, then something like this might work:


data class2;
  length name $100;
  set class; by sex;
  bold=2;
  output;

  if first.sex and sex = 'F' then do;
     name = 'Female students in the STEM Program';
	 call missing (of age, height, weight);
	 bold = 1;
	 output;
  end;
  else if first.sex and sex = 'M' then do;
     output;
     name='Male students in the STEM Program';
	 call missing (of age, height, weight);
	 bold = 1;
	 output;
  end;
run;

proc sort data=class2;
  by sex bold;
run;
  
options missing=' ';
proc report data=class2;
  column sex bold name age height weight;
  define sex / order noprint;
  define bold / display noprint;
  define name / display
         style(column)={width=3in j=l};
  define age / display;
  define height / display;
  define weight / display;
  compute after sex;
    line ' ';
  endcomp;
  compute name;
    if bold = 1 then call define(_col_,"style","style={font_weight=bold}");
  endcomp;
 run;

This is a very simplified code using SASHELP.CLASS just to show the different approaches.

Cynthia

Re: Expanind titles -not to wrap when the column width has been set

LNM — Tue, 11 Jun 2019 14:20:15 GMT

Here's the sample data. I would like the department names with rwidth=1 not to wrap

namefield

amount

Pos_Pool

jobfield

combo_cd

bold

rwidth

444444-Tesing the Namefiled if row width can be extended

1

1

112121-The Northwest Tornado Cha
sers

1

1

1

Mary McKee

10000

11112222

Administrative Assistant

ztornado-WA1

Mary McKee-1

200000

00011122

Administrative Assistant

ztornado-WA1

Mary McKee-2

155555

01112222

Administrative Assistant

725ztornado-WA1

Thank you,

Lile

Re: Expanind titles -not to wrap when the column width has been set

Cynthia_sas — Tue, 11 Jun 2019 14:26:08 GMT

Hi:
Usually when you provide data, it is in the form of a working DATA step program with DATALINES. And your program had MORE variables, Your program had:
page row bold rwidth namefield pos_pool jobfield amount fte combo_cd
where are the page and row variables???

Can you use the little notepad icon to post code and your DATALINES in a WORKING program.

But I believe my second code example answers your question about how to make the cell for NAME wide enough to accommodate a long string without wrapping.

Cynthia

Re: Expanind titles -not to wrap when the column width has been set

ballardw — Tue, 11 Jun 2019 15:40:41 GMT

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Expanind titles -not to wrap when the column width has been set

LNM — Wed, 12 Jun 2019 19:34:29 GMT

Sorry for the delay in getting back to you all. I still need help. The data comes to me in a excel spreadsheet. Then I run the print program. I have created an attached small sample. Thank you, thank you.

Re: Expanind titles -not to wrap when the column width has been set

LNM — Wed, 12 Jun 2019 19:40:23 GMT

Here's my attached codes in notepad

Re: Expanind titles -not to wrap when the column width has been set

LNM — Wed, 12 Jun 2019 19:48:35 GMT

I have more than 1000 departments. So I need to try to expand the namefield when the rwidth=1 is my best choice. Please look at my sample data which only contains 2 departments. Thank you, thank you.

Re: Expanind titles -not to wrap when the column width has been set

ballardw — Wed, 12 Jun 2019 22:36:19 GMT

@LNM wrote:

I have more than 1000 departments. So I need to try to expand the namefield when the rwidth=1 is my best choice. Please look at my sample data which only contains 2 departments. Thank you, thank you.

I repeat that you need to provide useable data. Your list of text does not provide 1) variable names, 2) lengths, 3) where a "line" might be multiple variables.

The code mentioned here will create a SAS data step in plain text from your data:

Re: Expanind titles -not to wrap when the column width has been set

heffo — Wed, 12 Jun 2019 23:12:46 GMT

I would continue with the code of @Cynthia_sas and make the width of the column based on the values in the actual selection.

data want;
	length name $100;
	set sashelp.class;
	bold = mod(_n_,3); *Make every third person bold in the proc report.;

	if mod(_n_,4) = 0 then do;
		name = cats(name,'-student with a long name'); *Make some names longer, change this to see what happens.;
	end;
run;

proc sql noprint; *Create a macro variable with the longest length of the name, multiply by 8 or so;
	select max(length(name))*8 into : name_length 
		from want;
quit;

proc report data=want;
	column sex bold name age height weight;
	define sex / order noprint;
	define bold / display noprint;
	define name / display
		style(column)={width=&name_length.px}; *Width changes based on the above macro variable.;
	define age / display;
	define height / display;
	define weight / display;

	compute after sex;
		line ' ';
	endcomp;

	compute name;

		if bold = 1 then
			call define(_col_,"style","style={font_weight=bold}");
	endcomp;
run;

Re: Expanind titles -not to wrap when the column width has been set

Cynthia_sas — Wed, 12 Jun 2019 23:24:55 GMT

Hi:

I just don't know what you mean when you say "expand the namefield".

PROC REPORT will NOT make NAMEFIELD wider on one row. The cell width for NAMEFIELD will have to be the SAME width on every row.

Seeing the first data that you posted (sorry I don't open XLSX files -- a DATALINES program is safer to open), it seems to me that you want the NAMEFIELD to be as wide as it needs to be for the longest value, and you don't want NAMEFIELD to wrap within the cellwidth.

Using some FAKEDATA, based on your original few rows, I created this output:

Here's the code I used:

data fakedata;
  length namefield $150 jobfield $50 combo_cd $50;
  infile datalines dlm=',';
  input page row namefield $ amount pos_pool jobfield $ combo_cd $ bold rwidth;
datalines4;
1,1,444444-Testing the Namefield if row width can be extended,1,1,112121-The Northwest Tornado Chasers,1,1,1
1,2,Mary McKee,10000,11112222,Administrative Assistant,ztornado-WA1,.,.
1,3,Mary McKee-1,200000,00011122,Administrative Assistant,ztornado-WA1,.,.
1,4,Mary McKee-2,155555,01112222,Administrative Assistant,725ztornado-WA1,.,.
2,1,454545-Testing the Namefield if row width can be extended,1,1,112121-The Northwest Comet Watchers,1,1,1
2,2,Alan Archer,10001,11112222,Sales Assistant,zcomet-WA2,.,.
2,3,Barbara Barton,200002,00011122,Administrative Assistant,zcomet-WA2,.,.
2,4,Clive Corleone,155553,01112222,CEO Assistant,725zcomet-WA2,.,.
;;;;
run;

 ods listing close;
options nonumber nodate orientation=portrait papersize=LETTER missing='';
title; footnote;
ods pdf file="c:\temp\testing_wide_cell.pdf";
 
proc report data=fakedata nowindows missing  split='|'
         style(report)={just=left cellpadding=2px}
         style(header)={font_size=9pt}
         style(column)={font_size=7pt};
  column page row  bold rwidth namefield pos_pool jobfield amount combo_cd;
  define page / order noprint order=data;
  define row / order noprint;
  define bold/order noprint;
  define rwidth / display noprint;
  define namefield/ display "NAME" style(column)={width=3in just=l};
  define pos_pool/ display "POSITION/POOL" style(column)={just=l};
  define jobfield/ display "POSITION TITLE" style(column)={width=2in just=l};
  define amount/ display "TOTAL" format=comma32.2 style(column)={just=r};
  define combo_cd/ display "DEPT/FUND" style(column)={just=r};
  compute bold;
    if bold eq 1 then call define(_row_,"style","style={font_weight=bold}");
  endcomp;
 run;
 ods pdf close;

Note that I made the font size for the columns 7pt and made the font size for the headers 9pt. Then I reduced the cellpadding in the cell, so that the long value for NAMEFIELD would fit in 3 inches.I got rid of the spacing=1 because it is a LISTING only option that would be ignored by ODS PDF. rather than "overcontrol" the cellwidths, I only adjusted the cellwidth for NAMEFIELD and JOBFIELD.

There is NOT a way in PROC REPORT to do the equivalent of "merge cells" in Excel.

Hope this gets you closer to your goal.

Cynthia