BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Niugg2010
Obsidian | Level 7

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
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

13 REPLIES 13
ballardw
Super User
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
SAS Super FREQ

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
Obsidian | Level 7

Thank you. Both ways work well.

Niugg2010
Obsidian | Level 7

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
SAS Super FREQ
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
Obsidian | Level 7
This link "Page unavailable"
Cynthia_sas
SAS Super FREQ
Sorry, somehow the "on pages 8-11" overlapped with the URL:
https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

cynthia
PaigeMiller
Diamond | Level 26

@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
Niugg2010
Obsidian | Level 7

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
SAS Super FREQ

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

Cynthia_sas
SAS Super FREQ

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
Obsidian | Level 7
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
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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