topic Re: Array in Proc Report to color in SAS Programming

Array in Proc Report to color

hhchenfx — Wed, 27 Mar 2019 15:34:01 GMT

Hi Everyone,

I have 2 sets of column, One set start with S_ and the other set start with V_

The idea is that I create 2 arrays and perform the coloring as below

Array S(*) S_:;

Array V(*) V_:;

Do i=1 to Dim(S);

if S(i)>V(i) then color the S(i) cell.

Can you please help me fixing my code?

Thanks a lot.

HHCFX


data have;
input ID S_axb S_a V_a V_acb ;
datalines;
1 1 1 1 1
2 1 2 1 0
3 1 2 3 1
4 9 8 7 4
;run;

proc report data=Have nowd;

  define S:/display;
  define V:/display;
  define dummy/display;

  compute dummy;
	 array v(*) V:;
	 array s(*) S:;
	 dummy=1;


	 do i=1 to dim(v);
		 if v{i}>s{i} then call define ("v{i}","style","style={background=RED}");
	 end;
  endcomp;

run;

Re: Array in Proc Report to color

andreas_lds — Wed, 27 Mar 2019 17:10:13 GMT

Afaik arrays are only available in the data step.

Re: Array in Proc Report to color

JerryV — Wed, 27 Mar 2019 17:58:39 GMT

You need a column statement in order to get compute dummy to work. Try this:

data have;
input ID S_axb S_a V_a V_acb ;
datalines;
1 1 1 1 1
2 1 2 1 0
3 1 2 3 1
4 9 8 7 4
;;;;

data _null_;
set have; 
  array v(*) V:;
  array s(*) S:;
  do i = 1 to dim(v); 
    v_Element=vname(v{i}); 
	s_Element=vname(s{i});
   file "!TEMP\define.sas" ;
	put 'define ' s_Element '/ display ;' 
	  / 'define ' v_Element '/ display ;';
   file  "!TEMP\compute.sas" ;
	put 'if ' v_Element '> ' s_Element 'then call define ("' v_Element +(-1)'","style","style={background=RED}");' ;
  end; 
stop; run;

proc report data=Have nowd;
column _ALL_ dummy;
%include "!TEMP\define.sas"  /source2;
  define dummy / noprint;

  compute dummy;
	 dummy=1;
     %include "!TEMP\compute.sas"  /source2;
  endcomp;
run;

Re: Array in Proc Report to color

PaigeMiller — Wed, 27 Mar 2019 19:45:39 GMT

@andreas_lds wrote:

Afaik arrays are only available in the data step.

In the past, I have used arrays in a PROC REPORT compute block, such as:

array c _c3_ _c4_ _c5_ _c6_ _c7_ _c8_ _c9_ _c10_;
do i=1 to dim(c);
    if c{i}<20000 then call define(i+2,'style','style={background=yellow}');
end;

So, the original poster @hhchenfx could adapt this to his problem.

Re: Array in Proc Report to color

andreas_lds — Wed, 27 Mar 2019 20:36:06 GMT

@PaigeMiller: thanks ... I should have looked it up before posting.

Re: Array in Proc Report to color

hhchenfx — Wed, 27 Mar 2019 20:58:20 GMT

I cant fix the issue of declaring array.

I try another way but still fail.

I hope to be able to do it in an easy to understand way.

proc report data=Have nowd;

  define S:/display;
  define V:/display;

	 array v(*) V:;
	 array s(*) S:;


	 do i=1 to dim(v);
		compute v{i};
			 if v{i}>s{i} then call define ("v{i}","style","style={background=RED}");
	  	endcomp;
	 end;

run;

Re: Array in Proc Report to color

hhchenfx — Wed, 27 Mar 2019 21:01:25 GMT

I simplify everything and the issue is the declare of Array.

50636 proc report data=Have nowd;
50637
50638 define V:/display;
50639
50640 array v(*) V:;
-----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
50641
50642 do i=1 to dim(v);
--
180
50643 compute v{i};
-
22
200
ERROR 180-322: Statement is not valid or it is used out of proper order.
ERROR 22-322: Syntax error, expecting one of the following: ;, /.
ERROR 200-322: The symbol is not recognized and will be ignored.
50644 if v{i}>0 then call define (i,"style","style={background=RED}");
50645 endcomp;
50646 end;
---
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
50647
50648 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

data have;
input ID S_axb S_a V_a V_acb ;
datalines;
1 1 1 1 1
2 1 2 1 0
3 1 2 3 1
4 9 8 7 4
;run;

proc report data=Have nowd;

  define V:/display;

	 array v(*) V:;

	 do i=1 to dim(v);
		compute v{i};
			 if v{i}>0 then call define (i,"style","style={background=RED}");
	  	endcomp;
	 end;

run;

Re: Array in Proc Report to color

hhchenfx — Wed, 27 Mar 2019 23:47:12 GMT

OK, after hours of debug, here is the thing:

In array, you have to list all variable. V: will not work!

Somehow, I cant get the dummy=1 inside the Proc Report works. So I just create this dummy in the data have.

To get column painted, I use the thisvar=vname(v(i)); and put thisvar in define statement.

That's all I can say.

Yes Array works

data have;
input ID S_axb S_a V_a V_acb ;
datalines;
1 1 1 1 1
2 1 2 1 0
3 1 2 3 1
4 9 8 7 4
;run;

data have ; set have;
dummy=1;run;

proc report data=Have nowd;
column S: V: dummy;
define dummy/display;
define V:/display;
define S:/display;


	compute dummy;
		length thisvar $10;
	 	array v(*) V_a V_acb;
	 	array s(*) S_axb S_a;

		do i=1 to dim(v);
			thisvar=vname(v(i));
			if V[i]<S(i) then call define (thisvar,"style","style={background=RED}");
		end;
	endcomp;

run;

Re: Array in Proc Report to color

hhchenfx — Thu, 28 Mar 2019 00:02:41 GMT

A bit more, put 2 list of name into macro variable

data have;
input ID S_axb S_a V_a V_acb ;
datalines;
1 1 1 1 1
2 1 2 1 0
3 1 2 3 1
4 9 8 7 4
;run;

data have ; set have;
dummy=1;run;

/*create macro variable for list of column*/
%let var_list1= V_a V_acb;
%let var_list2= S_axb S_a;

data _v; set have;
keep V:;
if _N_=1;run;

proc transpose data=_v out=_v (keep=_NAME_);run;
proc sql noprint;
select _NAME_ into :varlist1 separated by ' ' from _v;quit;
%put &varlist1;


data _s; set have;
keep s:;
if _N_=1;run;

proc transpose data=_s out=_s (keep=_NAME_);run;
proc sql noprint;
select _NAME_ into :varlist2 separated by ' ' from _s;quit;
%put &varlist2;


proc report data=Have nowd;
  column  S: V: dummy;
	define dummy/display;
	define V:/display;
	define S:/display;



	compute dummy;
		length thisvar $10;
	 	array v(*) &var_list1;
	 	array s(*) &var_list2;

		do i=1 to dim(v);
			thisvar=vname(v(i));
			if V[i]>S(i) then call define (thisvar,"style","style={background=RED}");
		end;
	endcomp;

run;