topic Re: I need to colour columns values while sending to excel (ods) in SAS Programming

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

nitink26 — Sat, 25 Feb 2023 04:36:29 GMT

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.

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

nitink26 — Sat, 25 Feb 2023 06:22:22 GMT

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;

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

Patrick — Sat, 25 Feb 2023 08:58:36 GMT

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-report-in/td-p/567573

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

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

PaigeMiller — Sat, 25 Feb 2023 11:18:23 GMT

@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.

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

nitink26 — Sun, 26 Feb 2023 08:37:08 GMT

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.

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

nitink26 — Sun, 26 Feb 2023 08:36:31 GMT

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;

sas dataset attached for your reference.

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

Patrick — Sun, 26 Feb 2023 12:11:46 GMT

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#p0xgxvo2we2mqrn1kteecpv1opx0

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;

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

PaigeMiller — Sun, 26 Feb 2023 11:43:18 GMT

@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.

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

Tom — Sun, 26 Feb 2023 15:41:14 GMT

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
;

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

nitink26 — Mon, 27 Feb 2023 03:19:59 GMT

Thanks Patrick!

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

nitink26 — Mon, 27 Feb 2023 03:32:38 GMT

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!

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

Patrick — Mon, 27 Feb 2023 06:47:33 GMT

@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/p16rdsa30vmm43n1ej4936nwa01t.htm

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

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

nitink26 — Mon, 27 Feb 2023 08:10:26 GMT

Thanks Patrick,

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

Anyways thankyou very much.

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

Patrick — Mon, 27 Feb 2023 08:40:21 GMT

@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()

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

Ksharp — Mon, 27 Feb 2023 12:00:08 GMT

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;