BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
nitink26
Obsidian | Level 7

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 -

nitink26_0-1677299620598.png

 

Dataset is attached for your reference.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

Patrick_1-1677411397792.png

 

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;

Patrick_0-1677413325728.png

 

View solution in original post

14 REPLIES 14
nitink26
Obsidian | Level 7

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
Diamond | Level 26

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

--
Paige Miller
nitink26
Obsidian | Level 7

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
Super User Tom
Super User

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
Opal | Level 21

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.

Patrick_0-1677315459179.png

 

nitink26
Obsidian | Level 7

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
Opal | Level 21

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

Patrick_1-1677411397792.png

 

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;

Patrick_0-1677413325728.png

 

nitink26
Obsidian | Level 7

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
Opal | Level 21

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

Patrick_0-1677480397361.png

Patrick_2-1677480435763.png

 

 

 

nitink26
Obsidian | Level 7

Thanks Patrick,

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

Anyways thankyou very much. 

Patrick
Opal | Level 21

@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
Diamond | Level 26

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

--
Paige Miller
Ksharp
Super User
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;

Ksharp_0-1677499188214.png

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1909 views
  • 2 likes
  • 5 in conversation