PROC Report & ODS excel: define columnwidth in function of #byval

[fre]

Dear community,

I'm learning the very interesting proc report with ODS excel.

Currently, I have a layout-issue I can't figure out and which Google can't help me.

I want to define the columnwidth of a variable in function of the sheet.

I've managed to create a multi-sheet excelfile, where each sheet has a sheetname based on the value of #byval1.

#byval1 can have 3 values:  X, Y and Z - so the name of the 3 sheets are respectively X, Y and Z.

What I want:  

if #byval1 = X then columnwidth of varB is 20  (this is sheet 'X')

if #byval1 = Y then columnwidth of varB is 40  (this is sheet 'Y')

if #byval1 = Z then columnwidth of varB is 60  (this is sheet 'Z)

This is my current code:

%macro make_excel;

ods excel 
file=outfile2 
options(sheet_name = "#byval1");

proc report 
data=base 
nowd 
out=abc
by varA;

column varA varB varC;

define varA / order ;
define varB / order style(column)=[width=65mm] ;
define varC / display;
run;

ODS EXCEL CLOSE;

%mend;

%make_excel

I've tried to replace the code for "define varB" with the following lines:

define varB / order
%if #byval1='X' %then %do; style(column)=[width=20mm]  %end;
%if #byval1='Y' %then %do; style(column)=[width=40mm]  %end;
%if #byval1='Z' %then %do; style(column)=[width=60mm]  %end;
;

but that doesn't do the job, it gives errors.

Is the approach I'm trying to use the correct one ?  

What am I missing here?

Any help much appreciated

[ballardw]

First "Why?". If you use SHEET_INTERVAL='BYGROUP' then each table of the Proc Report output should be written to a separate sheet and if the length of variables changes then the sizing might be automatic. Can't test without data though.

You may also need some more options as if your longer values have spaces in them the text may want to wrap in the cell regardless of cell width.

The only approach that comes to mind where this might work would be make separate calls to Proc Report filtering data at each call to that specific value using macro code to add in the conditional width.

That would not using BY processing so BYVAL wouldn't be available anywhere and would be using Where Vara="&somemacrovariable" where that macro variable holds the value to make the table. You would also have statements in the body of the proc report like

%if &somemacrovariable=X %then %do;
        define varB / order style(column)=[width=20mm] ;
%end;
%if &somemacrovariable=Y %then %do;
        define varB / order style(column)=[width=40mm] ;
%end;
%if &somemacrovariable=Z %then %do;
        define varB / order style(column)=[width=60mm] ;
%end;

This would require providing lists of values for the macro variable in some manner.

Calling @Cynthia_sas to see if she as more insight into possible approaches.

[fre]

Thank you for your reply.

Why?   Because there are indeed cases where the values are longer (with spaces) and proc report with ods excel automatically wraps these values, which I don't want to.

The only way I can prevent this, is to define a specific length for each byval ( or sheet) in the define of varB.

That's why I think I need to pass through the value of the byval to a macro-variable but I'm not sure how...

[data_null__]

Sounds like 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.

[Cynthia_sas]

HI:

  One of the challenges in what you're trying to do is that you can't test #byval in code. It's not a macro variable. It is designed to be used in a TITLE statement and does not "exist" in a place where you can test it in a PDV or in the REPORT procedure. There may be some way around it, but not using #BYVAL.

  But the other thing I find confusing is that VARB in an single dataset is going to have only one length, internally stored, in the descriptor portion of the data. Let's say that the internally stored length for VARB is 40.  So, if I understand what is described, it sounds like for one BY group, the actual max length of VARB could be 20, but for another BY group, the actual max length of VARB could be 5, and then for another BY group, the actual max length of VARB could be 30. What is wanted is NOT displaying the cell on each sheet with the maximum length, what is needed/wanted is a way to control the width for VARB based on the value of the BY variable. Is that correct????

Cynthia

[Tom · Accepted solution]

Since you want to use the value of BY var as the sheet name you could just generate separate PROC steps for each by value.  So if you have a driver table with the by values and the widths you want.

data groups;
  input width value $50. ;
cards;
20 X
40 Y
60 Z
;

Then use that to generate the PROC REPORT statements.

So perhaps you could setup a macro that does that and takes the width and value as input.

%macro bywidth(width,value);
ods excel options(sheet_name = &value);
proc report data=base nowd ;
  where varA = &value;
  column varA varB varC;
  define varA / order ;
  define varB / order style(column)=[width=&width.mm] ;
  define varC / display;
run;
%mend bywidth;

Then use the dataset to generate the calls to the macro.

ods excel file=outfile2 ;

data _null_;
  set groups;
  call execute(catx(' ','%nrstr(bywidth)(',width,',',quote(trim(value),"'"),');'));
run;

ods excel close;

[Cynthia_sas]

@Tom, that's a good approach. Another alternative is to have a user-defined format and then to use the BY variable in a BY statement AND in the COLUMN statement. That makes the value of the BY variable available to be used inside the PROC REPORT step. For this example, I created a fake VARB variable where the length changed for each value of the BY variable (I just took SASHELP.CLASS and created VARB by concatenating some fake string with NAME to give me variable lengths for each BY group:

  Note that in my case, AGE is an ORDER item. That means AGE is only put on the report on the first row of the group. So I make a temporary variable HOLDAGE, that will only get changed at the start of each BY group/ORDER group (which are the same). 

  Then I can make a style variable SVAR which can then be used as the style string I need for the CALL DEFINE for VARB. With the PUT function, I can make the style override use my FORMAT value for the WIDTH attribute.

  In this case, I only need one PROC REPORT step, I do need to have sorted data to use the BY statement and there is a bit of fiddling with temporary variables and COMPUTE blocks.

Cynthia

[fre]

Thank you very much @Tom , @Cynthia_sas for both of your solutions.

I've tried the code and it does exactly what I want.

Also thank you to all other members who contributed.

[fre]

Hi @Cynthia_sas ,

as I said in my previous answer, your solution does give the desired output.

However, when I run your code, the log keeps firing errors.

This is the complete code:  ( &link_shared_folder had been defined outside this code)

data fakedata;
  set sashelp.class;
  varb = catx(" ",name,age);
run;

proc sort data=fakedata;
  by age;
run;

proc format;
  value w_by  
   11="1.25in"
   12="1.25in"
			13="2.0in"
			14="3.0in"
			15="1.25in"
			16="1.25in"
			;
run;

options nobyline;

%let path=&link_shared_folder\SAS\ODS\;
ods excel file="&path\diff_width.xlsx"

options (sheet_name='Age #byval1');

proc report data=fakedata out=a123;
 by age;
 column age varb sex height weight;
 define age /order page;
 define varb / display ;
 define sex / display;
 define height / display;
 define weight / display;

 compute before age;
	holdage = age;
 endcomp;

 compute before _page_ /style=SystemTitle;
  line 'THIS BYGROUP is for Age: ' Age 2.0;
 endcomp;

 compute varb;
  length svar $50;
	svar = catt ('style={width=',put (holdage,w_by.),'}');
	call define (_col_,'style',svar);
 endcomp;

run;
ods excel close;

As I already said, the resulting excel-file is what I want, but in the log, I keep having these errors.

I've tried to understand what the cause is, but I can't find the reason.

The same error is repeated for each by-group.

Do you have any idea what's the culprit here?

1                                                          The SAS System                               13:35 Tuesday, June 20, 2023

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='xxxx';
6          %LET _CLIENTPROJECTPATHHOST='xxxx';
7          %LET _CLIENTPROJECTNAME='xxxx.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGHTML TEMP;
15         ODS HTML(ID=EGHTML) FILE=EGHTML
16             ENCODING='utf-8'
17             STYLE=HTMLBlue
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21         ;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
22         
23         GOPTIONS ACCESSIBLE;
24         
25         


26         data fakedata;
27         set sashelp.class;
28         varb = catx(" ",name,age);
29         
30         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.FAKEDATA has 19 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

31         
32         proc sort data=fakedata;
33         by age;
34         run;

NOTE: There were 19 observations read from the data set WORK.FAKEDATA.
NOTE: The data set WORK.FAKEDATA has 19 observations and 6 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

35         
36         proc format;
37         value w_by
38         11="1.25in"
39         12="1.25in"
2                                                          The SAS System                               13:35 Tuesday, June 20, 2023

40         			13="2.0in"
41         			14="3.0in"
42         			15="1.25in"
43         			16="1.25in"
44         			;
NOTE: Format W_BY is already on the library WORK.FORMATS.
NOTE: Format W_BY has been output.
45         run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

46         
47         options nobyline;
48         
49         %let path=&link_shared_folder\SAS\ODS\;
50         
51         
52         ods excel file="&path\diff_width.xlsx"
53         
54         options (sheet_name='Age #byval1');
55         
56         proc report data=fakedata out=a123;
57         by age;
58         column age varb sex height weight;
59         define age /order page;
60         define varb / display ;
61         define sex / display;
62         define height / display;
63         define weight / display;
64         compute before age;
65         	holdage = age;
66         endcomp;
67         compute before _page_ /style=SystemTitle;
68         line 'THIS BYGROUP is for Age: ' Age 2.0;
69         endcomp;
70         compute varb;
71         length svar $50;
72         	svar = catt ('style={width=',put (holdage,w_by.),'}');
73         	call define (_col_,'style',svar);
74         endcomp;
75         run;

NOTE: Variable Age already exists on file WORK.A123, using Age2 instead.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The above message was for the following BY group:
      Age=11
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
3                                                          The SAS System                               13:35 Tuesday, June 20, 2023

              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The above message was for the following BY group:
      Age=12
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The above message was for the following BY group:
      Age=13
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The above message was for the following BY group:
      Age=14
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The above message was for the following BY group:
      Age=15
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, a format name, !, *, @, DYNAMIC, EXPRESSION, RESOLVE, SYMGET, 
              _UNDEFINE_, _UNDEF_, _UND_, |.  
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The above message was for the following BY group:
      Age=16
NOTE: There were 19 observations read from the data set WORK.FAKEDATA.
NOTE: The data set WORK.A123 has 31 observations and 7 variables.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.29 seconds
4                                                          The SAS System                               13:35 Tuesday, June 20, 2023

      cpu time            0.20 seconds
      

76         ods excel close;