DATA Step, Macro, Functions and more

proc report procedure

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 94
Accepted Solution

proc report procedure

 

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.

 

 

Capture.JPG

 

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;


Accepted Solutions
Solution
‎09-25-2017 08:59 AM
Super User
Posts: 13,084

Re: proc report procedure

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

View solution in original post


All Replies
Solution
‎09-25-2017 08:59 AM
Super User
Posts: 13,084

Re: proc report procedure

Posted in reply to Niugg2010
proc report data=a;
column trt visno, (param, value);
define trt / group;
define visno / across;
define param /  across;
define value / analysis nozero;
run;
SAS Super FREQ
Posts: 9,263

Re: proc report procedure

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

Re: proc report procedure

Posted in reply to Cynthia_sas

Thank you. Both ways work well.

Frequent Contributor
Posts: 94

Re: proc report procedure

Posted in reply to Cynthia_sas

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;

SAS Super FREQ
Posts: 9,263

Re: proc report procedure

Posted in reply to Niugg2010
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
Frequent Contributor
Posts: 94

Re: proc report procedure

Posted in reply to Cynthia_sas
This link "Page unavailable"
SAS Super FREQ
Posts: 9,263

Re: proc report procedure

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

cynthia
Respected Advisor
Posts: 2,662

Re: proc report procedure

Posted in reply to Cynthia_sas

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

--
Paige Miller
Frequent Contributor
Posts: 94

Re: proc report procedure

Posted in reply to PaigeMiller

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;

SAS Super FREQ
Posts: 9,263

Re: proc report procedure

[ Edited ]
Posted in reply to PaigeMiller

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.

proc_report_question.png

 

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

SAS Super FREQ
Posts: 9,263

Re: proc report procedure

Posted in reply to 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;

Frequent Contributor
Posts: 94

Re: proc report procedure

Posted in reply to Cynthia_sas
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
Frequent Contributor
Posts: 94

Re: proc report procedure

Posted in reply to 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;

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 410 views
  • 2 likes
  • 4 in conversation