proc report procedure

[Niugg2010]

Below is my code and output. 

I do not want the red marked columns. 

I know if I use proc transpose to deal with my data and output each column, then I can get what I want. However this will make the code a litte more complicated and longer.

Does someone  know a better method to modify my code? Thanks.

data a;
input TRT $ Visno $ Param $ value;
datalines;
TRT1 v1 A 8
TRT1 v1 B 6
TRT1 v1 C 5
TRT1 v2 A 6
TRT1 v2 B 7
TRT1 v3 C 8
TRT2 v1 A 3
TRT2 v1 B 7
TRT2 v1 C 5
TRT2 v2 A 10
TRT2 v2 B 6
TRT2 v3 C 10
TRT3 v1 A 10
TRT3 v1 B 16
TRT3 v1 C 18
TRT3 v2 A 14
TRT3 v2 B 11
TRT3 v3 C 15
;

proc report data=a;
column trt visno, (param, value);
define trt / group;
define visno / across;
define param / across;
run;

[ballardw · Accepted solution]

proc report data=a;
column trt visno, (param, value);
define trt / group;
define visno / across;
define param /  across;
define value / analysis nozero;
run;

[Cynthia_sas]

Hi:

  As an alternative, you can also use the NOCOMPLETECOLS option, as shown below. In addition, I showed how to get rid of the repetition of the label for VALUE in the output.

cynthia

proc report data=a nocompletecols;
column ('Trt' trt) visno, (param, value);
define trt / group ' ';
define visno / across;
define param / across;
define value / sum ' ';
run;

[Niugg2010]

Thank you. Both ways work well.

[Niugg2010]

In addition I want to color coding "Value" column. Why the blow code does not work?

Thanks

data a;
input TRT $ Visno $ Param $ value flg;
datalines;
TRT1 v1 A 8 1
TRT1 v1 B 6 0
TRT1 v1 C 5 2
TRT1 v2 A 6 0
TRT1 v2 B 7 0
TRT1 v3 C 8 0
TRT2 v1 A 3 1
TRT2 v1 B 7 3
TRT2 v1 C 5 2
TRT2 v2 A 10 0
TRT2 v2 B 6 1
TRT2 v3 C 10 0
TRT3 v1 A 10 2
TRT3 v1 B 16 1
TRT3 v1 C 18 2
TRT3 v2 A 14 1
TRT3 v2 B 11 2
TRT3 v3 C 15 1
;
proc report data=a NOCOMPLETECOLS;
column trt visno, (param, (flg value ));

define trt / group;
define visno / across;
define param / across;
define flg / display;

compute value;
if flg=1 and above>0 then call define(_col_,"style","style=[background=yellow]");
else if flg=2 then call define(_col_,"style","style=[background=orange]");
else if flg=3 then call define(_col_,"style","style=[background=red]");
endcomp;
run;

[Cynthia_sas]

Hi,
VALUE and FLG are both under an across item and so in this usage you cannot use the simple variable name in your compute block. You need to use absolute column numbers, as shown in this paper, http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdfon pages 8 --11.

cynthia

[Niugg2010]

This link "Page unavailable"

[Cynthia_sas]

Sorry, somehow the "on pages 8-11" overlapped with the URL:
https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

cynthia

[PaigeMiller]

@Cynthia_sas, it's not that simple.

If you execute the code provided by @Niugg2010, you see that the log for PROC REPORT indicates that FLG is uninitialized. He is trying to color code his variable VALUE based upon the value of variable FLG. I don't see a way to do that, and also PROC REPORT tells us that FLG is uninitialized.

[Niugg2010]

Below is my code, still does not work.

by the way, if i use "%if &i  in (2,4,6,8,10,18) %then %do;" instead of 

%if &i =2 or &i=4 or &i=6 or &i=8 or &i=10 or &i=18 %then %do;

the code produces some errors. 

options mprint ;
proc report data=a;
column trt visno, (param, (flg value ));
define trt / group;
define visno / across;
define param / across;
define flg / display;

compute value ;
%macro flg;
%do i=2 %to 18;
%let k=%eval(&i+1);
%if &i =2 or &i=4 or &i=6 or &i=8 or &i=10 or &i=18 %then %do;
%put &i &k;

if _c&i._=1 then call define(_c&k._,"style","style=[background=yellow]");
if _c&i._=2 then call define(_c&k._,"style","style=[background=orange]");
if _c&i._=3 then call define(_c&k._,"style","style=[background=red]");
%end;
%end;
%mend;
%flg;
endcomp;
run;

[Cynthia_sas]

Hi,
Yes, you're right, he cannot test the value of FLG directly. However, with any nesting in the ACROSS area, there will be an absolute column number assigned for each unique combination of values. So, for example, consider SASHELP.PRDSALE -- not the most interesting data, but has some good variables for nesting in the ACROSS usage.

Here's an example.

code in the posting below.

As you can see, I have 2 variables nested in the ACROSS -- Region (EAST or WEST) and Prodtype (FURNITURE or OFFICE). In my PROC REPORT code, I cannot test whether the value of REGION is EAST or WEST. But I can test the absolute column number because the EAST columns are _c2_ and _c3_ and the WEST columns are _c4_ and _c5_. I also know that EAST/FURNITURE is _c2_ and EAST/OFFICE is _c3_ and likewise, WEST/FURNITURE is _c4_ and WEST/OFFICE is _c5_.

Hope this helps,

cynthia

(I will track down the location of the data for that presentation.)

[Cynthia_sas]

proc sort data=sashelp.prdsale out=consumer;
where division = 'CONSUMER';
by country region;
run;

proc report data=consumer;
  title '1) testing absolute columns for ACROSS items';
  column country region,prodtype,actual;
  define country / group;
  define region / across;
  define prodtype / across;
  define actual / sum;
  compute actual;
    if _c2_ gt 25000 then
       call define ('_c2_','style','style={background=lightyellow');
    if _c4_ gt 25000 then
       call define ('_c4_','style','style={background=lightgreen');
  endcomp;
run;

proc report data=consumer;
  column country region,prodtype,actual;
  title '2) also testing country value for highlight';
  define country / group;
  define region / across;
  define prodtype / across;
  define actual / sum;
  compute actual;
    if _c2_ gt 25000 then
       call define ('_c2_','style','style={background=lightyellow');
    if _c4_ gt 25000 then
       call define ('_c4_','style','style={background=lightgreen');
    if country = 'CANADA' and _c4_ lt 25000 then
       call define ('_c4_','style','style={background=lightred');
  endcomp;
run;

[Niugg2010]

In the page 11 you mentioned "To see the full code for the COMPUTE block, download the
ZIP file of programs that will out on the R&D website on support.sas.com". I can not find the link. Can you please offer me a link? Thanks

[Niugg2010]

Below code works. I forgot to add " ", in  _c.&K.  sorry for that. Thank you all.

if someone help me to optimize my code it will be perfect.

options mprint ;
proc report data=a nocompletecols;
column trt visno, (param, (flg value ));
define trt / group;
define visno / across;
define param / across;
define flg / display;

compute value ;
%macro flg;
%do i=2 %to 18;
%let k=%eval(&i+1);
%if &i =2 or &i=4 or &i=6 or &i=8 or &i=10 or &i=18 %then %do;
%put &i &k;
if _c&i._=1 then call define("_c&k._","style","style=[background=yellow]");
if _c&i._=2 then call define("_c&k._","style","style=[background=orange]");
if _c&i._=3 then call define("_c&k._","style","style=[background=red]");
%end;
%end;
%mend;
%flg;
endcomp;
run;