proc report issue

[Niugg2010]

Below is my code and ouput.

I transposed "xy" dataset into  "final" dataset, then used proc report to output.

If anyone know a way to output the data directly from 'xy' datasets with proc report. Thanks.

 

 /******CODE*****************************************/

/* Data part**/

data a;
trt="1"; output;
trt="2"; output;
trt="3"; output;
run;
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;

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

create table y as
select event, sum(num) as total
from x
group by event;

create table xy as
select a.*, b.total, round(a.num/b.total*100,0.1) as per
from x as a, y as b
where a.event=b.event
order by event, trt;
quit;

/*Data part end*/

proc transpose data=xy out=xy_1 prefix=num;
by event;
id trt;
var num;
run;

proc transpose data=xy out=xy_2 prefix=per;
by event;
id trt;
var per;
run;

data final;
merge xy_1 xy_2;
by event;
drop _name_;
run;

ods rtf file= "C:\XXXXX\123.rtf";
proc report data=final;
column event ("trt1" (num1 per1)) ("trt2" (num2 per2)) ("trt3" (num3 per3));
define num:/display "n" center;
define per:/ display "%" center;
run;
ods rtf close;

 /****************************************/

[Jagadishkatam · Accepted solution]

Without the transpose we could use the proc report on xy dataset to get the output as below, try

data xy_;
length trt1 $10.;
set xy;
trt1=cats('trt',trt);
run;

proc report data=xy_;
column event (trt1),(num per);
define event /group width=10;
define trt1 / ' ' across order=internal;
define num/ "n" center ;
define per/  "%" center;
run;

[Niugg2010]

Thanks. It works.

[Jagadishkatam]

Thanks,
Could I request you to please mark this answer correct, it will help everyone in the community to quickly point to the best answers.