I need to colour columns values while sending to excel (ods)

[nitink26]

Hi,

I have below data - 

ID	A1	A2	A3	A4	A5	A6	A7	VALUES
1	1	2	3	4	5	6	7	1,3,5
2	8	9	6	3	2	5	8	2,8,10
3	4	3	2	1	1	8	7	25,30,100
4	1	4	5	0	2	5	6	1,9,15

ID is unique is here and I want to color the cells from A1-A7 if any of the values in Values(column).

Final Output -

Dataset is attached for your reference.

[nitink26]

I just need to know, how to apply condition under compute as

I am applying below condition but it's giving me an error

compute A1;
IF A1 IN (VALUES)
THEN call define('A1', "style", "style=[background=lightgreen]");

endcomp;

[PaigeMiller]

---

@nitink26 wrote:

I just need to know, how to apply condition under compute as

I am applying below condition but it's giving me an error

compute A1;
IF A1 IN (VALUES)
THEN call define('A1', "style", "style=[background=lightgreen]");

endcomp;

---

It's giving you an error, but you don't tell us what the error is.

Whenever there is an error, show us what the LOG shows; we need the ENTIRE code in the LOG for this PROC (that's every single line, every single character, 100% of the log for this PROC). Do not show us selected parts of the log for this ROC.

And as @Patrick says, a quick search ought to find the answer.

[nitink26]

Hi Paige,

Full code  which gives an error- 

proc report data=test;
column ID A1 A2 A3 A4 A5 A6 A7 VALUES;

compute A1;
IF A1 IN (VALUES)
THEN call define('A1', "style", "style=[background=lightgreen]");

endcomp;
run;

but if I change the compute code below it will work but I don't want this - 

proc report data=test1;
column ID A1 A2 A3 A4 A5 A6 A7 VALUES;;

compute A1;
IF A1 IN ('1','3','5')

THEN call define('A1', "style", "style=[background=lightgreen]");

endcomp;
run;

I want for every row, it will use values from VALUES column and highlight  the value in A1 if it matches any of the values of column VALUES. We can use ID as well as it's a unique identifier and for every id, Columns VALUES value are same.

sas dataset attached for your reference.

[Tom]

If you want to test if a value is in a string use the INDEX, INDEXW, FIND or FINDW function.

data test;
  input a1 $ ;
  values='1 2 3';
  found = 0<findw(values,a1,' ','r');
cards;
1
3
5
;

[Patrick]

If you provide a data step creating the sample data and then the full Proc Report code then someone might spend the time to add the logic for conditional formatting. Else search with keywords like: Proc Report Conditional Formatting and you will get hits like below:

https://communities.sas.com/t5/New-SAS-User/how-to-provide-conditional-formatting-in-cell-in-proc-re... 

Looking at the code snippet you shared: You need to reference the row where you want to highlight cells using syntax like below.

[nitink26]

Hi Patrick,

Full code  which gives an error- 

proc report data=test;
column ID A1 A2 A3 A4 A5 A6 A7 VALUES;;

compute A1;
IF A1 IN (VALUES)
THEN call define('A1', "style", "style=[background=lightgreen]");

endcomp;
run;

but if I change the compute code below it will work but I don't want this - 

proc report data=test1;
column ID A1 A2 A3 A4 A5 A6 A7 VALUES;;

compute A1;
IF A1 IN ('1','3','5')

THEN call define('A1', "style", "style=[background=lightgreen]");

endcomp;
run;

I want for every row, it will use values from VALUES column and highlight  the value in A1 if it matches any of the values of column VALUES. We can use ID as well as it's a unique identifier and for every id, Columns VALUES value are same.

sas dataset attached for your reference.

[Patrick · Accepted solution]

So the problem to solve here is not conditional formatting but how to compare a value stored in one variable against a list of values stored in another variable. 

The syntax you're trying to use is not valid.

https://documentation.sas.com/doc/en/lrcon/9.4/p00iah2thp63bmn1lt20esag14lh.htm#p0xgxvo2we2mqrn1ktee... 

Below code that returns what you're after. It also provides the sample data via a SAS data step. If you provide sample data this way then you'll get the more answers with tested code.

What the forum taught me lately: The order of the variables in the column statement matters. If you want to use Value in the compute block for A1 then it Values needs to be listed before A1 in the column statement. 

data test1;
  infile datalines truncover dlm='|' dsd;
  input (id A1-A7) ($) values :$20.;
  datalines;
1|1|2|3|4|5|6|7|("1","3","5")
2|8|9|6|3|2|5|8|("2","8","10")
3|4|3|2|1|1|8|7|("25","30")
4|1|4|5|0|2|5|6|("1","9","15"")
;

ods excel file="%sysfunc(pathname(work))\test.xlsx";
proc report data=test1;
  column VALUES ID A1 A2 A3 A4 A5 A6 A7;
  compute A1;
    IF findw(values,strip(a1),,'kd') THEN
      call define(_col_, "style", "style=[background=lightgreen]");
  endcomp;
run;
ods excel close;

[nitink26]

Thanks Patrick!

[nitink26]

Hi Patrick,

if you don't mind, could you pls explain the logic(parameters) in the code you used. I know first two parameters -

findw(values,strip(a1),,'kd')

Thanks in advance!

[Patrick]

---

@nitink26 wrote:

Hi Patrick,

if you don't mind, could you pls explain the logic(parameters) in the code you used. I know first two parameters -

findw(values,strip(a1),,'kd')

Thanks in advance!

---

The documentation is your friend. https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p16rdsa30vmm43n1ej4936nwa01... 

What it does: Any character that isn't a digit is treated as word delimiter.

[nitink26]

Thanks Patrick,

I was unaware that we can use two at the same time like "kd".

Anyways thankyou very much. 

[Patrick]

---

@nitink26 wrote:

Thanks Patrick,

I was unaware that we can use two at the same time like "kd".

Anyways thankyou very much. 

---

You can use as many as you like. 

K works like a NOT. So whatever you define using the K option will revert the selection. The same applies for other string functions like compress()

[PaigeMiller]

---

@nitink26 wrote:

Hi Patrick,

Full code  which gives an error- 

---

I specifically asked to see the LOG. I want to see the ENTIRE log (every single line) for this PROC REPORT.

[Ksharp]

data test1;
  infile datalines expandtabs truncover dlm=' ' ;
  input id A1-A7 values :$20.;
  datalines;
1 1 2 3 4 5 6 7 1,3,5
2 8 9 6 3 2 5 8 2,8,10
3 4 3 2 1 1 8 7 25,30,100
4 1 4 5 0 2 5 6 1,9,15
;

ods excel file="%sysfunc(pathname(work))\test.xlsx";
proc report data=test1;
  column  ID A1-A7 VALUES;
  define _all_/display;
  compute VALUES;
    array x{*} A1-A7;
 do i=1 to dim(x);
    IF findw(values,strip(x{i}),,'kd') THEN
      call define(vname(x{i}), "style", "style=[background=lightgreen]");
 end;
  endcomp;
run;
ods excel close;