DATA Step, Macro, Functions and more

proc report output

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

proc report output

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

 

1.jpg

 

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;


Accepted Solutions
Solution
‎04-20-2017 03:19 PM
SAS Super FREQ
Posts: 8,743

Re: proc report output

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;

View solution in original post


All Replies
Solution
‎04-20-2017 03:19 PM
SAS Super FREQ
Posts: 8,743

Re: proc report output

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;
Contributor
Posts: 66

Re: proc report output

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;

SAS Super FREQ
Posts: 8,743

Re: proc report output

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
Contributor
Posts: 66

Re: proc report output

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.

  

SAS Super FREQ
Posts: 8,743

Re: proc report output

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
Contributor
Posts: 66

Re: proc report output

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

SAS Super FREQ
Posts: 8,743

Re: proc report output

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
Contributor
Posts: 66

Re: proc report output

Got it.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 150 views
  • 1 like
  • 2 in conversation