Proc report Custom variable name at summaries and conditional summary

[nj_sas]

I guess this question might be already posted but not able to find the solution.

proc format; value $gender 'F' = 'Female' 'M' = 'Male';run;

proc report data = sashelp.class;
column sex age name height weight;
define sex/group format = $gender.;
define age/group;

break after sex/summarize;
compute after sex/style(lines)={font_size=1pt cellpadding=0 cellheight=1pt}; line ' ';
sex = 'Total '|| trim(sex);
call define(_row_, "style", "style = [background = orange]");
endcomp;

break after age/summarize;
compute after age/style(lines)={font_size=1pt cellpadding=0 cellheight=1pt};
sex = '';
age = 'Total '|| trim(age);
call define(_row_, "style", "style = [background = lightgray]");
endcomp;

compute name;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

rbreak after/summarize;
run;

1st issue:
trying to get 'Total Male', 'Total Female' for column sex. Likewise, 'Total age' for the column age, at summary rows. I've also tried to put a length statement in the datastep before proc report, if that increases the length of the column in proc report.

2nd issue:
at the end of the report, need to get total heights and total weights of the names, highlighted in lightgreen color.

Thanks for your help Message was edited by: nj_sas

[Cynthia_sas]

Hi:
1) what is your destination of interest??? ODS HTML, RTF, PDF?? You do not show which destination you are interested in. CALL DEFINE with STYLE changes will not work in the LISTING window.

2) you show a change for STYLE(LINES) in your COMPUTE AFTER blocks for SEX and AGE, but you have no LINE statement -- STYLE(LINES) will only impact LINE statement output.

3) You are limited by the length of the variable in your assignment statement (such as where you want to concatenate the word TOTAL to the value for SEX or AGE. But there are 2 different issues, SEX has a length of 1 -- so you would have to make the length bigger or create a computed column for the SEX variable in SASHELP.CLASS and AGE is a numeric column and you can't assign a character string to a numeric variable. You should be seeing messages simlar to these in your LOG:
[pre]
NOTE: Groups are not created because the usage of Name is DISPLAY. To avoid this note, change
all GROUP variables to ORDER variables.
NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
2:24
NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
2:16
NOTE: Invalid numeric data, 'Total 11' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 12' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 13' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 14' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 15' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 11' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 12' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 13' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 14' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 15' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 16' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 11' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 12' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 13' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 14' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 15' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 11' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 12' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 13' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 14' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 15' , at line 2 column 16.
NOTE: Invalid numeric data, 'Total 16' , at line 2 column 16.
[/pre]

The NOTEs for "Invalid numeric data" are trying to tell you that you can't assign a text string to the column for AGE.

4) Also confused why you have GROUP as the usage for SEX and AGE, but then show each NAME?? The NOTE about changing the usage to ORDER tells you how to fix or get rid of that note.

cynthia

[nj_sas]

1) I am trying to get the report in excel using excel XP tagset
2) edited the program with line statement
3) As age is numeric, for now I can go with putting total to only sex variable
4) changed the variables from group to order

the modified code:

data class;
length sex $20.;
set sashelp.class;
run;
proc format; value $gender 'F' = 'Female' 'M' = 'Male';run;

proc report data = class;
column sex age name height weight;
define sex/order format = $gender.;
define age/order;

break after sex/summarize;
compute after sex/style(lines)={font_size=1pt cellpadding=0 cellheight=1pt}; line ' ';
sex = 'Total '|| trim(sex);
call define(_row_, "style", "style = [background = orange]");
endcomp;

compute name;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

rbreak after/summarize;
run;

1. But. I am still seeing only 'Total' instead of 'Total Female' and 'Total Male'.

2. trying to figure out getting total heights and weights of the names with highlighted lightgreen color (issue(2) ).

Thanks

[Cynthia_sas]

Hi:
Sometimes, it is better to reassign values in the COMPUTE for the variable itself (such as a COMPUTE block for SEX) instead of in a COMPUTE after -- which I show in the modified program below.

Also, you can put a STYLE override directly on your BREAK or RBREAK statement, as well as in a CALL DEFINE statement and this works quite nicely. What you see is that the more specific CALL DEFINE for ORANGE, overrode the less specific STYLE on the BREAK statement for SEX -- which was set to a shade of gray. I made noticeable colors, so you should be able to change them to what you want.

In addition, I created output for ODS HTML and ODS TAGSETS.EXCELXP --- just so you could compare the way the output looks in the browser versus how the output looks in Excel. The color changes should come through OK, but your CELLHEIGHT on the LINE statement may not work in TAGSETS.EXCELXP in the same way that it works in HTML and the browser.

And, I went ahead and put in a BREAK for AGE. However, since you were already creating a longer value for the SEX variable in a DATA step program, I created a character variable version of AGE for use with PROC REPORT to simplify making a custom summary label at the break.

cynthia

[pre]
data class;
length sex $20 char_age $10;
set sashelp.class;
char_age = put(age,2.0);
run;

proc format;
value $gender 'F' = 'Female'
'M' = 'Male';
run;

ods html file='c:\temp\showhilite.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\showhilite.xls' style=sasweb;
proc report data = class nowd;
column sex age char_age name height weight;
define sex/order f=$gender.;
define age/order noprint;
define char_age / order;

break after sex/summarize
style={background=cxcccccc};

compute sex;
if upcase(_break_) = 'SEX' then do;
sex = 'Total '|| trim(put(sex,$gender.));
call define(_row_, "style", "style = [background = orange]");
end;
else if upcase(_break_) = 'CHAR_AGE' then sex = ' ';
endcomp;

break after char_age / summarize
style={background=yellow};
compute char_age;
if upcase(_break_) = 'CHAR_AGE' then do;
Char_Age = 'Total '||trim(left(char_age));
call define(_col_,"style", "style={font_weight=bold}");
end;
endcomp;

compute after sex/
style(lines)={font_size=1pt cellpadding=0 cellheight=1pt};
line ' ';
endcomp;

compute name;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

rbreak after/summarize
style={background=pink};
run;

ods _all_ close;
[/pre]

[nj_sas]

Cynthia,

I ran this code and seeing just "Total" instead of "Total Male" and "Total Female". I've also tried creating new increased length column of sex. Not sure what am I missing here ?

I am still wondering about the possibility of my 2 nd issue is with getting 'total weight' of the highlighted names as shown below, after the table summary (pink row) ?

compute name;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

Thanks

[Cynthia_sas]

Oh, sorry, my bad. I cut and pasted the wrong code. What I meant to post was a program that compared pre-processing in a DATA step program -and- using COMPUTED items. But I mis-remembered the name I'd used for saving. If you compare the two outputs, you will see that there's not a lot of difference between the 2 approaches. I changed the colors and the summary labels in the second approach to show "All Genders" and "All Ages" for the RBREAK.

cynthia
[pre]

proc format;
value $gender 'F' = 'Female'
'M' = 'Male';
run;

data class;
length gender $20 char_age $10;
set sashelp.class;
char_age = put(age,2.0);
gender = put(sex,$gender.);
run;


title;
ods html file='c:\temp\showhilite.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\showhilite.xls' style=sasweb;
proc report data = class nowd;
title '1) Preprocessing data in DATA step program';
column sex gender age char_age name height weight;
define sex/order f=$gender. noprint;
define gender /order;
define age/order noprint;
define char_age / order;

break after gender/summarize
style={background=cxcccccc};

compute gender;
if upcase(_break_) = 'GENDER' then do;
gender = 'Total '|| trim(gender);
call define(_row_, "style", "style = [background = orange]");
end;
else if upcase(_break_) = 'CHAR_AGE' then gender = ' ';
endcomp;

break after char_age / summarize
style={background=yellow};
compute char_age;
if upcase(_break_) = 'CHAR_AGE' then do;
Char_Age = 'Total '||trim(left(char_age));
call define(_col_,"style", "style={font_weight=bold}");
end;
endcomp;

compute after sex/
style(lines)={font_size=1pt cellpadding=0 cellheight=1pt};
line ' ';
endcomp;

compute name;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

rbreak after/summarize
style={background=pink};
run;


** 2 Using COMPUTED variables instead of DATA step;
proc report data = sashelp.class nowd;
title '2) Proc Report using computed items, and NOPRINT (no pre-processing)';
column sex age gender showage name height weight;
define sex/order noprint ;
define age/order noprint ;

define gender /computed 'Gender';
define showage / computed 'Age'
style(column)={just=r};

break after sex/summarize;
compute gender / character length=30;
gender = put(sex,$gender.);
if upcase(_break_) = 'AGE' then gender = ' ';
else if upcase(_break_) = 'SEX' then do;
gender = catx(' ','Total '||trim(put(sex,$gender.)));
call define(_row_, "style", "style = [background = orange]");
end;
endcomp;

compute after sex/style={font_size=1pt cellpadding=0 cellheight=1pt};
line ' ';
endcomp;

break after age/summarize;
compute showage / character length=10;
showage = put(age,2.0);
if upcase(_break_) = 'AGE' then do;
showage = catx(' ','Total ',trim(put(age,2.0)));
call define(_row_, "style", "style = [background = lightgray just=l]");
end;
endcomp;

compute name;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

rbreak after/summarize style={background=lightgreen};
compute after;
gender = 'All Genders';
showage = 'All Ages';
endcomp;
run;

ods _all_ close;
[/pre]

[nj_sas]

Thanks ! my need is to get total height for the selected names (highlighted in lightgreen color in the below code). I am not sure if it is possible or should I create a separate dataset and proc report and then append it to 1 st proc report.

data class;
length name $20.;
set sashelp.class;
name = name;run;

proc report data = class nowd;
title '2) Proc Report using computed items, and NOPRINT (no pre-processing)';
column sex name height weight;
define sex/order noprint ;
break after sex/summarize ;
compute name / character length=30;
if upcase(_break_) = 'SEX' then do;
name = '--> '||trim(put(sex,$gender.));
call define(_row_, "style", "style = [background = orange]");
end;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

compute after sex/style={font_size=1pt cellpadding=0 cellheight=1pt};
line ' ';
endcomp;

rbreak after/summarize style={background=pink};
compute after;
name = 'All Genders';
endcomp;
run;

I am trying to put a code something like below in the above code (not sure if that is possible)
compute after;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald') then do;
Name = 'Selected Names';
height = height.sum;end;
endcomp;
run;

or data step and 2nd proc report.

[Cynthia_sas]

I don't entirely understand what you mean when you say you need to get the total height for ONLY those names. I'm not sure whether you still want the regular break line or not -- I still see a break for the SEX variable and an RBREAK for everything.

When you have a BREAK statement with the SUMMARIZE option, PROC REPORT summarizes EVERY cell in the analysis COLUMN giving you the statistic that is on the DEFINE statement. By default, the usage for numeric variables is SUM. That means, you MUST use a compound name when you reference these analysis variables. So if you have the equivalent of:
[pre]
DEFINE HEIGHT / SUM;
[/pre]

(which would be the default treatment that REPORT would give HEIGHT if you do NOT have a DEFINE statement.) The only valid way to point to the summarized value for HEIGHT is by using the "compound name" HEIGHT.SUM in any COMPUTE block. (Just as, if you had requested the MEAN statistic for HEIGHT, then the valid compound name would be: HEIGHT.MEAN)

If you wanted to only add up the heights for the trafficlighted names, you'd have to do something quite different. For example, in a simpler example, note how I make an extra summary line for only Alice, Janet, and William heights....in addition to having a summary row for everyone, I create a "temporary" variable inside PROC REPORT (HOLDHEIGHT) that is incremented for only those 3 names. A temporary variable is automatically retained and does NOT have to be on the COLUMN statement.

Next, in order to have an "extra" summary line at the bottom for only those 3 names, I need an "extra" variable whose value is the same on every row, so that BREAK AFTER EXTRA can happen right before the RBREAK. Only the summary for BREAK AFTER EXTRA would be the same as the RBREAK total, except I've been accumulating HOLDHEIGHT...so in a COMPUTE block for EXTRA, I can swap HOLDHEIGHT in place of HEIGHT.SUM.

I -think- something like this is what you want.

cynthia
[pre]
** need to make an "extra" variable that is the same for every observation;
** in order to have an "extra" break line before the RBREAK summary;
data class;
length name $30;
set sashelp.class;
extra = 'x';
run;

ods listing close;
ods html file='c:\temp\only3.html' style=sasweb;
proc report data=class nowd;
title 'Extra Break Line with Total of Alice, Janet and William Heights';
column extra name age height weight;
define extra / order noprint;
define name / order;
define age / display;
define height /sum;
define weight /display;
compute height;
if name in ('Alice', 'Janet', 'William') then do;
holdheight + height.sum;
call define("name",'style','style={background=lightgreen}');
call define(_col_,'style','style={background=lightgreen}');
end;
endcomp;
break after extra / summarize style={background=lightgreen};
compute after extra;
** do the swap;
name='Total Green Heights';
height.sum = holdheight;
endcomp;
rbreak after/ summarize style={background=orange};
compute after;
name = 'Total All Heights';
endcomp;
run;
ods _all_ close;
[/pre]

[nj_sas]

This is exactly what I was looking for except it would be better if we can have totals of all numeric variables in the rbreak. What I am trying is to get the total height and total weight for all the rows (in the rbreak statement). But only get total heights (not weights) for the traffichighlighted names.

[Cynthia_sas]

Hi:
If you don't want the WEIGHT.SUM to show on the BREAK line with the "green" summary of heights, then you'll need to


1) change the usage of WEIGHT to SUM on the DEFINE statement so you get the summary on the RBREAK line and


2) set WEIGHT.SUM to missing on the "green" line or on the BREAK for EXTRA. And set the MISSING= option to display ' ' instead of . for missing values (to avoid seeing a . in the cell for WEIGHT.SUM.


 

Shown below are only the revisions to the previously posted program.


cynthia
[pre]
ods listing close;
ods html file='c:\temp\only3_with_weight.html' style=sasweb;
options missing=' ';
proc report data=class nowd;
title 'Extra Break Line with Total of Alice, Janet and William Heights';
title2 'Show total Weights for RBREAK, but only HEIGHTS for these 3';
column extra name age height weight;

... more code ...

define height /sum;
define weight /sum;

... more code ...

compute after extra;
name='Total Green Heights Only';
height.sum = holdheight;
weight.sum = .;
endcomp;
... more code ...

ods _all_ close;
options missing = .;
title;
[/pre]

[nj_sas]

Cynthia,

Thank you very much. It works for me. Looking for a small additional bells&whistle stuff here. trying to get both break before and break after in the below code. But don't need to get calculations for break before statement, meaning no height or no weight. The output I am looking for is

Name Height Weight
Female
Alice 56.5 84
... ... ...
... ... ...
Total Female 545.3 811
MaleAlfred 69 112.5
... .... ...
... .... ...
Total Male 639.1 1089.5
All Genders 1184.4 1900.5

proc report data = class nowd out= test;
column sex name height weight;
define sex/order noprint ;

break before sex/nosummarize; /*I know this option is not possible but something that serves the purpose*/

break after sex/summarize ;
compute name ;
if upcase(_break_) = 'SEX' then do;
name = '--> '||trim(put(sex,$gender.));
call define(_row_, "style", "style = [background = orange]");
end;
if name in ('Jane','Alice','Mary','James','Jeffrey','William','Ronald')
then call define (_col_, "style", "style = [background = lightgreen]");
endcomp;

compute after sex/style={font_size=1pt cellpadding=0 cellheight=1pt};
line ' ';
endcomp;

rbreak after/summarize style={background=pink};
compute after;
name = 'All Genders';
endcomp;
run;

[Cynthia_sas]

Hi:
I guess I don't understand the purpose of the BREAK BEFORE???? As you said, "nosummarize" is NOT an option. What are you trying to achieve???? It is not clear to me from what you posted.

This report seems very similar to several of the other examples I posted, except you don't have AGE on this report.

cynthia

[Cynthia_sas]

Oh, wait, I think I figured it out....

You do NOT need a BREAK statement to perform a COMPUTE BEFORE with a LINE statement.

cynthia
[pre]
proc format;
value $gender 'F' = 'Female' 'M' = 'Male';
run;

ods html file='c:\temp\compute_before.html' style=sasweb;
proc report data = sashelp.class nowd ;
column sex name height weight;
define sex/order noprint ;

break after sex/summarize ;

compute before sex /style={just=left font_weight=bold color=purple};
line sex $gender.;
endcomp;

run;
ods html close;
[/pre]