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;
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;
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;
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;
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.
Thanks. I did not realize missing options. Can we use missing="N/A" for numberic column also?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.