topic Re: proc report procedure in SAS Programming

proc report procedure

Niugg2010 — Fri, 22 Sep 2017 22:12:35 GMT

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;

Re: proc report procedure

ballardw — Fri, 22 Sep 2017 22:45:01 GMT

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

Re: proc report procedure

Cynthia_sas — Sat, 23 Sep 2017 01:34:54 GMT

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;

Re: proc report procedure

Niugg2010 — Mon, 25 Sep 2017 12:58:52 GMT

Thank you. Both ways work well.

Re: proc report procedure

Niugg2010 — Tue, 26 Sep 2017 15:49:01 GMT

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;

Re: proc report procedure

Cynthia_sas — Tue, 26 Sep 2017 20:29:23 GMT

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

Re: proc report procedure

Niugg2010 — Tue, 26 Sep 2017 22:17:18 GMT

This link "Page unavailable"

Re: proc report procedure

Cynthia_sas — Wed, 27 Sep 2017 12:22:25 GMT

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

cynthia

Re: proc report procedure

PaigeMiller — Wed, 27 Sep 2017 12:47:22 GMT

@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.

Re: proc report procedure

Niugg2010 — Wed, 27 Sep 2017 13:39:46 GMT

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

Re: proc report procedure

Niugg2010 — Wed, 27 Sep 2017 14:10:39 GMT

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;

Re: proc report procedure

Niugg2010 — Wed, 27 Sep 2017 14:30:44 GMT

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;

Re: proc report procedure

Cynthia_sas — Wed, 27 Sep 2017 15:39:12 GMT

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.)

Re: proc report procedure

Cynthia_sas — Wed, 27 Sep 2017 15:35:03 GMT

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;