Basic Proc Report

[mmm7]

I'm trying to create a simple report and would like feedback on whether processing needs to happen within data steps or can be written within proc report. I'm providing a very simple dataset as an example. As well as a visual made in Excel with a description of how ideally I would like the report to look. 

data example;
	input stats $4. count num;      
	cards;
Miss 1 1
Miss 2 4
Miss 3 2
Miss 4 2 
Miss . 4 
Min . 1
Max . 4
run;

[ChrisNZ]

Here's to get you started:

data EXAMPLE;
  input STATS $4. COUNT NUM;      
  cards;
Miss 1 1
Miss 2 4
Miss 3 2
Miss 4 2 
Miss . 4 
Min  . 1
Max  . 4
run;

data WANT;
  if _N_=1 then do;
    set EXAMPLE(rename=(NUM=MISSNUM));
    where STATS='Miss' and COUNT=.;
  end;
  set EXAMPLE;
  if STATS='Miss' and  COUNT and NUM =MISSNUM then COLOR='green ';
  if STATS='Miss' and  COUNT and NUM^=MISSNUM then COLOR='red   ';
  if STATS='Miss' and ^COUNT                  then COLOR='yellow';
run; 

STATS	COUNT	NUM	COLOR
Miss	1	1	red
Miss	2	4	green
Miss	3	2	red
Miss	4	2	red
Miss	.	4	yellow
Min	.	1
Max	.	4

[mmm7]

Thank you for your reply though this is not the solution. I am trying to create a report and this only creates a data set. 

[andreas_lds]

What have you tried?

There are numerous paper explaining how to set background colours in proc report.

[mmm7]

Not going to post my syntax that did not produce the results I would like. I've read multiple papers. I'm looking for help on my post, not unhelpful comments. Thanks.

[ChrisNZ]

I'm looking for help on my post, not unhelpful comments.

People in this community are volunteering their time and competence to guide you and help you solve your problems, so you can learn. Not to solve your problems for you. So more gratefulness and more gracefulness would be appropriate. @andreas_lds is one of 65 super users, the community's most helpful experts. Please keep this in mind. 🙂

[mmm7]

I very much appreciate when members provide helpful comments., as both @Kathryn_SAS and @Cynthia_sas did. Thank you!!  

[ChrisNZ]

I am trying to create a report and this only creates a data set.

As you said, it's a basic report. You now have all the data you need to build it.

[mmm7]

Great, if you can share syntax to create the report, as described, that would be helpful.

[Kathryn_SAS]

How about this:

data example;
	input stats $4. count num;      
	cards;
Miss 1 1
Miss 2 4
Miss 3 2
Miss 4 2 
Miss . 4 
Min . 1
Max . 4
;
run;

ods listing close;
ods excel file='c:\temp\test.xlsx';
data WANT;
  if _N_=1 then do;
    set EXAMPLE(rename=(NUM=MISSNUM));
    where STATS='Miss' and COUNT=.;
  end;
  set example;
run;

proc report data=want;
column stats count num missnum dummy;
define stats / display;
define count / display;
define num / display;
define missnum / display;
define dummy / computed noprint;

compute dummy;
if STATS='Miss' and COUNT=. then 
 call define(_row_,'style','style=[backgroundcolor=yellow]');
else if stats='Miss' and count and NUM =MISSNUM then 
 call define('_c3_','style','style=[backgroundcolor=green]');
else if stats='Miss' and count and NUM ne MISSNUM then 
 call define('_c3_','style','style=[backgroundcolor=red]');
endcomp;
run;

ods excel close;
ods listing;

[mmm7]

Very helpful! Is there a way to suppress the MISSNUM column from appearing on the report? Or does it need to be present in order to produce the expected results?

[Kathryn_SAS]

You can add the NOPRINT option as follows:

define missnum / display;

define missnum  / display noprint; 

[mmm7]

aha! I was using the noprint but not...display noprint. Thank you for responding. Your comments were very helpful and I was able to produce a beautiful report!

[Cynthia_sas]

Hi:

  If you're going to put the color variable and the MISSNUM variable in a dataset, then it will be fairly easy to write the PROC REPORT code to do the highlighting as you show. The challenge is going to come in when you want to add the break lines. PROC REPORT will not arbitrarily insert blank lines into a report. PROC REPORT can only insert break lines at a break between group or order variables. So, the issue with what you show is that you are going to need some helper variables to do break processing.

  However, you do not need to create a separate color variable in the dataset, but you will need to create a separate ordering/breaking variable. In my approach, I used a global macro variable to hold the value of MISSNUM=4 and then used that macro variable in the PROC REPORT COMPUTE block.

  Note that I also used an extra variable called ORDVAR in the original data creation program to ensure the order of the rows so that you could use a LINE statement to insert the blank line.

  Here's an example of the ORDVAR helper variable I used:

data fakedata;
    length stats $4;
    infile datalines dlm=',' dsd;
	input ordvar $ stats $ count num;   
    if stats = 'Miss' and Count = . then do;
       call symputx('wantnum',put(num,2.0),'G');
    end; 
	cards;
A,Miss,1,1
A,Miss,2,4
A,Miss,3,2
A,Miss,4,2
B,Miss,.,4
C,Min,.,1
C,Max,.,4
;
run;

  Then having the macro variable and the helper variable ORDVAR allowed me to run this PROC REPORT:

Cynthia