The SAS Output Delivery System and reporting techniques

Proc report conditional column.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Proc report conditional column.

Hi,

I must create a report in html where the column are printed if the variable meet specific criteria.

ex:

if A ne B then print A's column and B's column else noprint A's column and B's column.

 

 


Accepted Solutions
Solution
‎04-27-2017 07:30 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Proc report conditional column.

You do need to do some work yourself, I am not going to go through every combination with an answer Smiley Happy

Maybe something like:

data have;
  infile datalines dlm="," dsd;
  input name $ age a b c d;
datalines;
alan,12,59.44,45.17,3,4
barb,13,,,,      
carl,14,67.23,69.65,,
;
run;

data loop;
  set have;
  length vlist $2000;
  array v{4} a b c d;
  do i=1 to 4;
    if v{i} ne . then vlist=catx(' ',vlist,vname(v{i}));
  end;
  if vlist ne "" then 
    call execute('proc report data=have nowd; 
                    where name="'||strip(name)||'";
                    columns name age '||strip(vlist)||';
                  run;');
run;

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,646

Re: Proc report conditional column.

Run a data step before the proc report that sets the values as specified.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Proc report conditional column.

Thats a simple datastep then proc report:

data want;
  set have;
  if a ne b then result=a;
run;
proc report data=want;
  columns result;
run;

If this is not what you need, follow the good posting guidance below you post - post test data in the form of a datastep, and what you want the output to look like.

Occasional Contributor
Posts: 6

Re: Proc report conditional column.

[ Edited ]

Hi,

I start from this

 

 

data input1;
   set &input;
   by provcom;
 
   if errato=1;
   put  _N_=provcom= first.provcom = last.provcom ;
    
   if first.provcom then

     call execute('
options   leftmargin=1.0in rightmargin=1.0in orientation=landscape ;

ods HTML file="&report1_arcoh.'||strip(regprovcom)||'.HTML"  ;
ods escapechar="^";
 
proc report data=&input ( firstobs='||_N_||'obs='||_N_+&nmes.-1||' ) headline headskip style(column) = {just=c  fontstyle=roman   color= black font_size=9.5pt font_face="arial"   }  style(header) =  {just=c backgroundcolor=#B9D3EE fontstyle=roman   color= black font_size=9.5pt font_face="arial"   }   missing ;


columns  reg prov com nome mes d7a d7b totm microtot natig micronatig mortig micromortig d7bi apri d7bc aprc  ;

define reg /order "Cod Reg";
define prov /order "Cod Prov";
define com /order "Cod Com";
define nome /order "Descrizione";
define mes/ display "Mese" style(column)=[just=l] format=$mes.;
define d7a /display "Modello/D7a" style(column)={width=.60in} format=$D. ;
define d7b /display "Modello/D7b" style(column)={width=.60in} format=$D.;
define totm /display "Matrimoni/D7a" style(column)={width=.60in}  format=missing.;
define microtot /display "Matrimoni/D3" style(column)={width=.60in} format=missing.;
define natig /display "Nati/D7b" style(column)={width=.60in} format=missing.;
define micronatig /display "Nati/P4" style(column)={width=.60in} format=missing.;
define mortig /display "Morti/D7b" style(column)={width=.60in} format=missing.;
define micromortig /display "Morti/P5" style(column)={width=.60in}  format=missing.;
define d7bi /display "Iscritti/D7b" style(column)={width=.60in} format=missing. ;
define apri /display "Iscritti/Apr4" style(column)={width=.60in}  format=missing.;
define d7bc /display "Cancellati/D7b" style(column)={width=.60in}format=missing.;
define aprc /display "Cancellati/Apr4" style(column)={width=.60in} format=missing.;

compute d7a;
if d7a eq "X" then call define ("d7a" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
endcomp;
compute d7b;
if d7b eq "X" then call define ("d7b","style","style={ BACKGROUNDCOLOR=#EED5D2   }");
endcomp;

compute microtot ;
      if totm ne microtot then do;
                        call define ("totm" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
                        call define ("microtot" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
      end;
endcomp;


compute micronatig ;
      if natig ne micronatig then do;
                        call define ("natig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
                        call define ("micronatig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
      end;
endcomp;

compute micromortig ;
      if mortig ne micromortig then do;
                        call define ("mortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
                        call define ("micromortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
      end;
endcomp;

compute apri ;
      if d7bi ne apri then do;
                        call define ("d7bi" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
                        call define ("apri" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
      end;
endcomp;


compute aprc ;
      if d7bc ne aprc then do;
                        call define ("d7bc" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
                        call define ("aprc" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2   }");
      end;
endcomp;



title1 j=l ''^S={postimage="http://www.leggiillustrate.it/wp-content/uploads/2013/06/istat.gif" }''  ;
title2 j=l  font= "roman" height=10pt color=black "Dipartimento per la raccolata dati e lo sviluppo di tecnologie ";                                                                                             
title3 j=l  font= "roman" height=10pt color=black "per la produzione e diffuzione dell''informazione statistica ";
title4 " ";
title5 " ";
title6 j=l font= "arial" height=16pt color=black "Statistiche demografiche      Anno 2017" ;
title7 " ";                                                                                             
title8 " ";
title9 j=l font= "arial" bold  height=14pt color=SlateGray4 " X=Modelli mancanti D7a D7b" ;                    
title10 " ";                                                                                             
title11 " ";    run;           ');;



if last.provcom then call execute('ods pdf close;');
run;

 

This print all the column, now i want to print only the column with BACKGROUNDCOLOR=#EED5D2  for every html.

 

Thanks.

SAS Super FREQ
Posts: 8,719

Re: Proc report conditional column.

[ Edited ]

I see one big problem with this -- you have an ODS HTML with FILE=, but at the bottom, you have ODS PDF CLOSE;, where I would expect to see ODS HTML CLOSE: Do you want HTML or PDF output?

Next, I'm not sure what is in &INPUT, but since it is the same file that you use in your PROC REPORT step, you may need to use a different macro processing technique to build a COLUMN statement that contains just the variables you want based on your tests.

You use options in your PROC REPORT step (headline, headskip) that are LISTING only options and ignored by HTML, PDF, RTF, etc, so those are unnecessary.

You say that you want to "print only the column with BACKGROUNDCOLOR=#EED5D2 for every html." However, that is not a condition that can be checked. But, since setting the color seems to be based on logic, for example, you have this:
compute micromortig ;
   if mortig ne micromortig then do;
     call define ("mortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
     call define ("micromortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
   end;
endcomp;


So it seems that if mortig ne micromortig, then you would want to see both of these variables in the COLUMN statement? So it is THIS condition that you would need to use in your macro logic to build the COLUMN statement dynamically and NOT using COLOR as a selection criteria.

cynthia

SAS Super FREQ
Posts: 8,719

Re: Proc report conditional column.

Hi:

  As others have pointed out, without some sample data to test or seeing the program you've already tried, it is hard to make a constructive suggestion.

 

  However, you can assign value to a column in PROC REPORT conditionally. Please see these results for some fake data:

a_or_b.png

 

  As you can see in #1, the value of the column (cleverly named a_or_b) is either the value or A or the value of B based on a comparison. If you did not want to see the original A or the original B on the report, then you can use NOPRINT, as shown in report #2. However, if the goal is to have an output data set with a new column whose value is conditionally assigned, then the DATA step approach is all you need.

 

cynthia

 

** here is the code;

data new;
  infile datalines;
  input name $ age a b;
datalines;
alan 12  59.44 45.17   
barb 13  51.21 54.14  
carl 14  67.23 69.65 
edna 15  69.51 72.38  
fred 16  68.99 60.95 
;
run;

footnote 'Yellow means A was GT B; Pink means A LE B';
proc report data=new;
  title '1) showing all columns';
  column name age a b a_or_b;
  define name / order;
  define a / display;
  define b / display;
  define a_or_b / computed f=7.2;
  compute a_or_b;
    if a gt b then do;
          a_or_b = a;
	  call define(_col_,'style','style={background=lightyellow}');
	end;
	else if a le b then do;
	    a_or_b = b;
	    call define(_col_,'style','style={background=lightpink}');
	end;
  endcomp;
run;
  
proc report data=new;
  title '2 Using NOPRINT';
  column name age  a b a_or_b;
  define name / order;
  define a / display noprint;
  define b / display noprint;
  define a_or_b / computed;
  compute a_or_b;
    if a gt b then do;
        a_or_b = a;
		call define(_col_,'style','style={background=lightyellow}');
	end;
	else if a le b then do;
	    a_or_b = b;
		call define(_col_,'style','style={background=lightpink}');
	end;
  endcomp;
run;

Occasional Contributor
Posts: 6

Re: Proc report conditional column.

Hi,

thank you for your answer but unfortunately it's not the solution. I try to explain better my problem.

My dataset

name     $ age     a           b         c         d

 

alan       12        59.44   45.17     3        4

barb       13                                  1        2      

carl        14        67.23   69.65

edna      15 

fred        16       68.99 6    0.95    4        5

 

I want to produce one report for alan

name

$age

a

b

c

d

alan

12

59.44

45.17

3

4

One for barb

name

$age

c

d

barb

13

1

2

One for carl

name

$age

a

b

carl

14

67.23

69.65

Nothing for edna

And one for Fred

name

$age

a

b

c

d

fred

16

68.99

60.95

4

5

 

Thank you!

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Proc report conditional column.

Ok, then something simple like generating the code should be enough.  Note in the below how I put the test data, please do this in future:

data have;
  infile datalines dlm="," dsd;
  input name $ age a b c d;
datalines;
alan,12,59.44,45.17,3,4
barb,13,,,1,2      
carl,14,67.23,69.65,,
;
run;

data loop;
  set have;
  length vlist $2000;
  array v{4} a b c d;
  do i=1 to 4;
    if v{i} ne . then vlist=catx(' ',vlist,vname(v{i}));
  end;
  call execute('proc report data=have nowd; 
                  where name="'||strip(name)||'";
                  columns '||strip(vlist)||';
                run;');
run;

 

Occasional Contributor
Posts: 6

Re: Proc report conditional column.

Thank you RW9,

how can I print name and age in the  report?

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Proc report conditional column.

Just add in after columns:

data have;
  infile datalines dlm="," dsd;
  input name $ age a b c d;
datalines;
alan,12,59.44,45.17,3,4
barb,13,,,1,2      
carl,14,67.23,69.65,,
;
run;

data loop;
  set have;
  length vlist $2000;
  array v{4} a b c d;
  do i=1 to 4;
    if v{i} ne . then vlist=catx(' ',vlist,vname(v{i}));
  end;
  call execute('proc report data=have nowd; 
                  where name="'||strip(name)||'";
                  columns name age '||strip(vlist)||';
                run;');
run;
Occasional Contributor
Posts: 6

Re: Proc report conditional column.

Ok, but if I have

 

data have;
  infile datalines dlm="," dsd;
  input name $ age a b c d;
datalines;
alan,12,59.44,45.17,3,4
barb,13,,,,      
carl,14,67.23,69.65,,
;
run;

you crete the report for barb but I don't want a report if a b c d are blank.

 

 

Solution
‎04-27-2017 07:30 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Proc report conditional column.

You do need to do some work yourself, I am not going to go through every combination with an answer Smiley Happy

Maybe something like:

data have;
  infile datalines dlm="," dsd;
  input name $ age a b c d;
datalines;
alan,12,59.44,45.17,3,4
barb,13,,,,      
carl,14,67.23,69.65,,
;
run;

data loop;
  set have;
  length vlist $2000;
  array v{4} a b c d;
  do i=1 to 4;
    if v{i} ne . then vlist=catx(' ',vlist,vname(v{i}));
  end;
  if vlist ne "" then 
    call execute('proc report data=have nowd; 
                    where name="'||strip(name)||'";
                    columns name age '||strip(vlist)||';
                  run;');
run;
Occasional Contributor
Posts: 6

Re: Proc report conditional column.

Thank you so much! This works.

 

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 236 views
  • 0 likes
  • 4 in conversation