Variable unintialized when setting conditional formatting

[edmondwu516]

Hi,

I am working on a report and ran into the variable uninitialized error. I tried lots of different ways but still can't fix this so hoping i can get some help here.

Below is my data.

data a;

input channel $ date date9. min_price;

cards;

CHANNEL1 01JAN2017 500

CHANNEL2 01JAN2017 350

CHANNEL1 02JAN2017 300

CHANNEL2 02JAN2017 900

CHANNEL1 03JAN2017 800

CHANNEL2 03JAN2017 750

CHANNEL1 04JAN2017 900

CHANNEL2 04JAN2017 1000

;

run;

I am trying to create a report using PROC REPORT and show the minimum price for each channel on every day. Then I want to highlight the cells that are 'CHANNEL1' and having minimum price <=400. However, i got the min_price is uninitialized error and the output has the whole 'CHANEL1' row highlighted. What am I doing wrong here and how can I fix this? Any inputs will be appreciated.

proc report data=test;

column channel date,min_price;

define channel / 'Channel' group;

define date / 'Date' across;

define min_price / 'Minimum Price' min;

compute min_price;

   if channel='CHANNEL1' and min_price<=400 then call define(_col_,"style","style=[fontweight=bold background=green foreground=white]");

run;

Update:

Another solution:

data test;
input channel $ date date9. min_price;
cards;
CHANNEL1 01JAN2017 500
CHANNEL1 02JAN2017 300
CHANNEL1 03JAN2017 800
CHANNEL1 04JAN2017 350
CHANNEL2 01JAN2017 350
CHANNEL2 02JAN2017 900
CHANNEL2 03JAN2017 750
CHANNEL2 04JAN2017 1000
;
run;

proc format;
   value minfore 
      low-400 = 'white'
      other = 'black' ;
   value minback 
      low-400 = 'green'
      other = 'white' ;
run; 

proc report data=test;
column channel date,min_price;
define channel / 'Channel' group;
define date / 'Date' across f=date9.;
define min_price / 'Minimum price' analysis sum style(column)=[fontweight=bold background=minback. foreground=minfore.];
 
compute channel;
   if channel='CHANNEL2' then call define(_row_,"style","style=[fontweight=medium background=white foreground=black]");
endcomp;

run;

[Reeza]

Your code has some errors, here's the clean version but has the same error. Here's the corrected version someone else can start with.

data test;
input channel $ date date9. min_price;
cards;
CHANNEL1 01JAN2017 500
 
CHANNEL2 01JAN2017 350
CHANNEL1 02JAN2017 300
CHANNEL2 02JAN2017 900
CHANNEL1 03JAN2017 800
CHANNEL2 03JAN2017 750
CHANNEL1 04JAN2017 900
CHANNEL2 04JAN2017 1000
;
run;

proc report data=test;
column channel date min_price;
define channel / 'Channel' group;
define date / 'Date' across;
define min_price / 'Minimum Price';
 
compute min_price ;
   if channel='CHANNEL1' and min_price <= 400 then call define(_col_,"style","style=[fontweight=bold background=green foreground=white]");
endcomp;
run;

[edmondwu516]

Thank you for he reply. But your code is only showing the number of obs by channel and date plus an additional column of min price. I want to show the min price for each channel for each date value. My code is creating the report I want but it's not highlighting the cell correctly. 

[Reeza]

Your code had errors, I was simply trying to correct the errors so it would run. 

You had no ENDCOMP and a comma where you didn't need one. 

Feel free to post the "correct code" that doesn't highlight correctly and I can delete/remove mine. 

[edmondwu516]

I see. Thank you for pointing out. My code in SAS has the endcomp but I forgot to put it here 🙂

[ballardw]

---

@edmondwu516 wrote:
I see. Thank you for pointing out. My code in SAS has the endcomp but I forgot to put it here 🙂

---

It may help to copy and paste into the codebox opened using the forum {i} icon. The main message windows tend to reformat text, such as removing leading blanks, so code indents don't appear and make it easy to not see a missing closing statement such as endcomp or end if you use indenting in your code.

[Cynthia_sas · Accepted solution]

Hi:

  If you only have 1 row per channel/date combination, then you don't need MIN on the DEFINE statement for MIN_PRICE. But if you want the min_price value underneath each date, then your original use of the comma in the COLUMN statement was correct, it was the reference in the CALL DEFINE to min_price that was getting your the error. When you have one variable under an ACROSS variable, such as you originally showed, then you can't use the variable name, you have to use the absolute column number. I think something like this is what you want:

  I finagled the data for Jan 4 so that there would be 2 cells highlighted for CHANNEL1.

  The code that produced that screen shot was:

data test;
input channel $ date date9. min_price;
cards;
CHANNEL1 01JAN2017 500
CHANNEL1 02JAN2017 300
CHANNEL1 03JAN2017 800
CHANNEL1 04JAN2017 350
CHANNEL2 01JAN2017 350
CHANNEL2 02JAN2017 900
CHANNEL2 03JAN2017 750
CHANNEL2 04JAN2017 1000
;
run;

proc report data=test;
column channel date,min_price;
define channel / 'Channel' group;
define date / 'Date' across f=date9.;
define min_price / 'Minimum Price' analysis sum;
 
compute min_price ;
   if channel='CHANNEL1' then do;
      if _c2_ <= 400 then call define('_c2_',"style","style=[fontweight=bold background=green foreground=white]");
      if _c3_ <= 400 then call define('_c3_',"style","style=[fontweight=bold background=green foreground=white]");
      if _c4_ <= 400 then call define('_c4_',"style","style=[fontweight=bold background=green foreground=white]");
      if _c5_ <= 400 then call define('_c5_',"style","style=[fontweight=bold background=green foreground=white]");
   end;
endcomp;
run;

Note how the _c2_ etc absolute column numbers are used in the COMPUTE block.

cynthia

[edmondwu516]

Cynthia,

Thank you very much! This is exactly what I was looking for and you helped me understand more about the mysterious PROC REPORT. 🙂

I also came up with another solution, which is creating a custom format for the min_price variable then set the format for the CHANNEL2 back to default.

data test;
input channel $ date date9. min_price;
cards;
CHANNEL1 01JAN2017 500
CHANNEL1 02JAN2017 300
CHANNEL1 03JAN2017 800
CHANNEL1 04JAN2017 350
CHANNEL2 01JAN2017 350
CHANNEL2 02JAN2017 900
CHANNEL2 03JAN2017 750
CHANNEL2 04JAN2017 1000
;
run;

proc format;
   value minfore 
      low-400 = 'white'
      other = 'black' ;
   value minback 
      low-400 = 'green'
      other = 'white' ;
run; 

proc report data=test;
column channel date,min_price;
define channel / 'Channel' group;
define date / 'Date' across f=date9.;
define min_price / 'Minimum price' analysis sum style(column)=[fontweight=bold background=minback. foreground=minfore.];
 
compute channel;
   if channel='CHANNEL2' then call define(_row_,"style","style=[fontweight=medium background=white foreground=black]");
endcomp;

run;

[edmondwu516]

Cynthia,

Thank you very much! This is exactly what I was looking for and you helped me understand more about the mysterious PROC REPORT.

I also came up with another solution, which is creating a custom format for the min_price variable then set the format for the CHANNEL2 back to default.

data test;
input channel $ date date9. min_price;
cards;
CHANNEL1 01JAN2017 500
CHANNEL1 02JAN2017 300
CHANNEL1 03JAN2017 800
CHANNEL1 04JAN2017 350
CHANNEL2 01JAN2017 350
CHANNEL2 02JAN2017 900
CHANNEL2 03JAN2017 750
CHANNEL2 04JAN2017 1000
;
run;

proc format;
   value minfore 
      low-400 = 'white'
      other = 'black' ;
   value minback 
      low-400 = 'green'
      other = 'white' ;
run; 

proc report data=test;
column channel date,min_price;
define channel / 'Channel' group;
define date / 'Date' across f=date9.;
define min_price / 'Minimum price' analysis sum style(column)=[fontweight=bold background=minback. foreground=minfore.];
 
compute channel;
   if channel='CHANNEL2' then call define(_row_,"style","style=[fontweight=medium background=white foreground=black]");
endcomp;

run;