PROC REPORT: Conditional formatting based on value of ACROSS variable

[JordiGC]

Hello:

Is there any way to reference a cell to the right or to the left of the present value? I'm trying to compare the value of a cell with the value of the cell to the left of it, but I haven't been able to make it work in an elegant way. I have the following code:

	proc report data= ControlDiario showall out=columnas;
		where Central = "&PowerPlant";
		columns ('VLE' '' 'Superaciones' Dia) Grupo, ModoFuncionamiento, Medida, VLE, 
			Superaciones, (VLEComparacion Valor);
		define Dia / group order=internal 
			style(column)={width=160 tagattr='type:DateTime format:DD/MM/YYYY'};
		define Grupo / across '' format=&FormatoGrupos preloadfmt order=data;
		define ModoFuncionamiento / across nozero '';
		define Medida / across '' format=$FormatoMedida. preloadfmt order=data;
		define Superaciones / across '';
		define VLE / across '';
		define VLEComparacion / noprint;
		compute Valor;
			call symputx('ColValor', cats('_c', _col_, '_'));
			call symputx('ColVLE', cats('_c', _col_ - 1, '_'));
			if symget('ColValor') > symget('ColVLE') then do;
				call define(_col_, 'style', 'style=[background=very_light_vivid_red]');
			end;
		endcomp;

I thought ColValor would get the number of the present column (_col_) and I could compare it with the column to its left (ColVLE with value _col_ - 1), but the macro variables are not getting the right values.

On the other side, this is working, but I have to create a big array every time:

	proc report data = ControlMensual;
		columns ("VLE" '' 'Superaciones' Mes) Grupo, ModoFuncionamiento, Medida, 
			VLE, Superaciones, (VLEComparacion Valor);
		define Mes / group format=FormatoMeses. preloadfmt order=data;
		define Grupo / across '' format=&FormatoGrupos preloadfmt order=data;
		define ModoFuncionamiento / across nozero '';
		define Medida / across '' format=$FormatoMedida. preloadfmt order=data;
		define Superaciones / across '';
		define VLE / across '';
		define VLEComparacion / noprint;
		compute Valor;
			array Columnas(2000) 
			%do i = 1 %to 2000; _c&i._ %end;;
			if Columnas(_col_) > Columnas(_col_ - 1) then 
				call define(_col_, 'style', 'style=[background=very_light_vivid_red]');
		endcomp;
	run;

Do you know any way to resolve the name of the macros in the first code so that I can compare the present column with another one to the left of it?

Thanks and regards,

Jordi 

[Cynthia_sas]

Hi: You have added a new post to a Forum thread that started in 2012 and was modified in 2017. The answer is still the same as originally posted. I do not think you can use CALL SYMPUTX and then SYMGET in the same COMPUTE block, but that would be a question for SAS Tech Support. Your second attempt with an ARRAY statement and a %DO loop doesn't make sense to me. I would expect that you're getting some kind of error in the COMPUTE block, something like:
ERROR: The %DO statement is not valid in open code.
WARNING: Apparent symbolic reference I not resolved.
ERROR: The %END statement is not valid in open code.

Because the %DO loop belongs in a SAS macro program. And the other issue I see is that you have at least 5 nestings of ACROSS items with 2 variables under each nesting. Then you make one of the variables NOPRINT -- but even as a NOPRINT item, your VLEComparacion variable will be assigned an absolute column number and so will the Valor variable. Even when you make a variable NOPRINT, it will get an absolute column number. So I think your second attempt will not work either. You might want to work with SAS Tech Support so you can show them ALL your data (or a subset) and ALL your formats and ALL your code and work with them on the best suggestion for how to do what you want to do. Also, I would suggest starting a new post in the forum. While your example does use ACROSS variables, your usage is so different from the original 2012 posting, that it really deserves to be a new post.
Cynthia

[JordiGC]

Thanks you for your answer, even if the thread has some years now.
About the second solution, it actually works :). I just need to make it more efficient.

[ballardw]

Three suggestions:

1) Start your own thread. If you want to reference this thread you can copy the URL from your browser and paste into the text of your problem description. One thing as author of a thread you can select a response as a solution, assuming one is provided.

2) Provide a small data set in the form of data step OR reference one of the SAS supplied data sets in the SASHELP library that provides enough variables to do something similar to what you want.

3) With that example data set provide a table, such as manually made in a word processor, showing the behaviors you want.

Very complex comparisons might be better done before Proc Report (or other report procedure) hence the requests for data and desired appearance.

[JordiGC]

Thanks for your suggestions ballardw. I will do so next time. There is no need to open a new thread for this topic now.