topic Proc report group variables in same column in ODS and Base Reporting

Proc report group variables in same column

PeterCulhane — Mon, 24 Aug 2020 13:58:35 GMT

I would like to create a table like this, where the sex variable is under the name variable, and the sex variable does not have a column to itself. I do not want to create separate tables or have any blank rows. The code below, using 'noprint' and adding the 'F' in column definition and 'M' in a line in 'compute' is going the right direction, but currently gives me an 'M' before the males and at the end of the report. I do not want the 'M' at the end of the report.

I'm using SAS EG 7.4.

So I want this (I've added the ... to make this post shorter):

Name	Age
F
Alice	13
Barbara	13
…
M
Alfred	14
Henry	14

...

Code so far:

proc sort data=sashelp.class out=edu;
by sex name;
run;

proc report data=edu;
columns ('F' Sex Name ) Age;
define sex/group '' noprint;
define name/group;
define age/analysis;
compute after sex ;
   line 'M' @1 ;
   endcomp;
run;

Output from code above (with ... added):

F
Name	Age
Alice	13
Barbara	13
…
M
Alfred	14
Henry	14
…
William	15
M

I'm using ODS excel then to output to an Excel file that I don't want to edit again.

Many thanks, and apologies if this has been asked before or I've left out essential details.

Re: Proc report group variables in same column

Cynthia_sas — Tue, 25 Aug 2020 00:57:40 GMT

Hi:
You need to use a COMPUTE block. See this paper: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf particularly page 9, so you can see all the places you can write an extra line of text or line with a variable value using PROC REPORT.
Then the examples on pages 18-20 approx show the use of COMPUTE BEFORE to do what you want to do, I think.
Hope this helps,
Cynthia

Re: Proc report group variables in same column

PeterCulhane — Tue, 25 Aug 2020 12:08:27 GMT

Very many thanks Cynthia, that worked.

I used this code from your paper successfully to suppress one value of a variable, but I don't understand how it worked.

...
if region = 'Canada' then reglg = 0;
 else reglg = 1;
 endcomp;
 compute after region/ style=Header;
 line brktxt $varying. reglg;
...

I understand setting the length to 0 will stop SAS from printing a blank line

But I don't understand how SAS knows to take the value of reglg as the length.

Many thanks again for your help.

Re: Proc report group variables in same column

Cynthia_sas — Tue, 25 Aug 2020 12:51:33 GMT

Hi:
Setting the value to 0 in the REGLG variable doesn't do anything by itself until I use the REGLG variable with the $VARYING format for writing variable length text. I can specify $VARYING's length as $VARYING100. (which means that the line could be anywhere in length from 1-100, but not greater than 100. Or I can specify $VARYING length-variable -- as I do in the case of the LINE statement in that example. $VARYING is very cool with PROC REPORT because setting a LENGTH of 0 for the length variable will cause REPORT to suppress the LINE text.
Cynthia