proc report output

[Niugg2010]

I have another question about proc report:
I want to get below output. 

I transposed x dataset into x2 dataset, then used proc report to output each column. Does anyone know how to output directly from X dataset?

Thanks

Below is my code.

data a;
trt="1"; output;
trt="2"; output;
trt="3"; output;
run;
data b;
event="a"; num=2; output;
event="b"; num=3; output;
event="c"; num=4; output;
run;
data c;
group="G1"; output;
group="G2"; output;
run;

proc sql;
create table x as
select c.*, a.*, b.*
from a, b,c
order by b.event, c.group, a.trt;
quit;

proc transpose data=x out=x2(drop=_name_) prefix=trt;
by event;
id trt group;
var num;
run ;

proc report data=x2;
column ("event" event) ("group1" ("trt1" trt1g1)("trt2" trt2g1)("trt3" trt3g1))
("group2" ("trt1" trt1g2)("trt2" trt2g2)("trt3" trt3g2));
define trt:/"";
define event/"";
run;

[Cynthia_sas · Accepted solution]

Hi:

  Well, I changed your program that makes data for WORK.X because I never name variables GROUP, ACROSS or ORDER, to avoid confusion with PROC REPORT syntax. What you called GROUP, I used the name GRPVAR. 

  Without doing a transpose, I can replicate your desired output using ACROSS items in PROC REPORT as shown in the code below.

cynthia

** used variable name of GRPVAR to avoid;
** confusion with GROUP usage in PROC REPORT;
data c;
grpvar="G1"; output;
grpvar="G2"; output;
run;
  
proc sql;
create table x as
select c.*, a.*, b.*
from a, b,c
order by b.event, c.grpvar, a.trt;
quit;

proc report data=x;
  column ('Event' event) grpvar,trt,num;
  define event / group ' ';
  define grpvar/across ' ';
  define trt / across ' ';
  define num / sum ' ';
run;

[Niugg2010]

Thanks. The key point is to define event/ group, otherwise the display will be wrong.

I want to go further based on my initial question.

Below is my code. I added a "Per" column. if the Per column is numberic, the code worked fine. However if I changed it into Character, the code produced some error "ERROR: There is no statistic associated with per."

Can you please help me to figure out? Thanks

data a;
trt="1"; output;
trt="2"; output;
trt="3"; output;
run;
data b;
event="a"; num=2; output;
event="b"; num=3; output;
event="c"; num=4; output;
run;
data c;
grpvar="G1"; output;
grpvar="G2"; output;
run;
proc sql;
create table x as
select c.*, a.*, b.*, put(b.num/10,5.1) as per
from a, b,c
order by b.event, c.grpvar, a.trt;
quit;

proc report data=x;
column ('Event' event) grpvar,trt,(num per);
define event / group ' ';
define grpvar/across ' ';
define trt / across ' ';
run;

[Cynthia_sas]

Hi:
I guess I don't understand why would you change the PER column to be character? You are asking for a summary report when you use the GROUP usage for EVENT and ACROSS for GRPVAR and TRT.

It doesn't make sense to summarize a character string. So, PROC REPORT is not broken, when it refuses to summarize a character string. When PER is numeric, PROC REPORT can summarize the PER column and nest it underneath each GRPVAR,TRT column.

If you need to nest character strings, that is a more advanced usage of ACROSS and does not seem relevant here. However, if you REALLY want to nest character variables under ACROSS items, please see this paper http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf specifically, at pages 4 and 5 at the Team Captain example.

cynthia

[Niugg2010]

Thanks. If it is numeric variable, the missing data will show ".", and it will be blank for character variable. For some report we do not wnat to see ".", so we need to change numeric into character. Actully, using format can also tick off "." issue.

I have figured out the error issue in my question. If we add a dummy variable, it will not show the error. I am not clear why we need a dummy variable. Do you know that? Thanks.

[Cynthia_sas]

But your underlying assumption is incorrect. To have missing numeric variables display as a blank you only need
options missing=' ';
before the PROC REPORT step.

cynthia

[Niugg2010]

Thanks. I did not realize missing options. Can we use missing="N/A" for numberic column also?

[Cynthia_sas]

Not in PROC REPORT or other procedures. The MISSING= system option allows only 1 character. If you want to see N/A in PROC REPORT, then you need a format.

TABULATE has a misstext= option that would support MISSTEXT='N/A' -- but not PROC REPORT.

cynthia

[Niugg2010]

Got it.