Proc report compute not returning desired result

[HabAM]

I have used similar code in other reports and works fine. But for some reason I can't manage to get desired outcome with this. Can anyone help? 

Thank you

ods _all_ close;
ods tagsets.ExcelXP file='\\xxxxxxxx.XLS' style=Printer;
title '......';
ods tagsets.excelxp options(sheet_label = 'rrrrrrr');

proc report data=has nofs center;
columns id service Race Gender  Insurance paid_date value;
label paid_date ='Paid Date';

compute paid_date ;
if ((value >50 and paid_date >0) or (value <50 and paid_date =.)) and value ne . then do;
call define(_col_,'STYLE','STYLE=[background=yellow]');
end;
endcomp;
title 'Insurance';
run;
ods tagsets.ExcelXP close;

[ballardw]

Without input data and what the report should actually look like for that input it is pretty hard to guess where things might be not-quite-right.

Is it an appearance issue such as borders, color, font or calculated result?

I'm going to guess that you aren't getting many values from your compute block:

compute paid_date ;
if ((value >50 and paid_date >0) or (value <50 and paid_date =.)) and value ne . then do;
   call define(_col_,'STYLE','STYLE=[background=yellow]');
end;

Maybe you want:

compute paid_date ;
if ((value >50 and paid_date >0) or (. < value <50 and paid_date =.))  then do;
   call define(_col_,'STYLE','STYLE=[background=yellow]');
end;

What happens if value = 50?

[Cynthia_sas]

Hi:

  BallardW is correct, without data, no one can work on your code or make constructive suggestions.

  One thing I notice is that in your COLUMN statement, you have:

columns id service Race Gender  Insurance paid_date value;

  However, it is in the COMPUTE block for paid_date that you are testing VALUE. First of all if VALUE is a numeric variable, with the default usage of SUM, then you MUST refer to it with the COMPOUND name of value.sum. Only if VALUE has a usage of DISPLAY can you use the simple reference to VALUE in the COMPUTE block.

  But, you have a bigger problem. Remember that PROC REPORT works from left to right when it builds a report row, so for example if you have this COLUMN statement:

column grpvar charvar numvar1 numvar2 numvar3 numvar4;

  Then in the COMPUTE block for NUMVAR2, you can only test the value of NUMVAR2 and the values for the items to the LEFT of NUMVAR2 (GRPVAR, CHARVAR, NUMVAR1 and NUMVAR2). In the COMPUTE block for NUMVAR2, you CANNOT, CANNOT, CANNOT test the values of NUMVAR3 or NUMVAR4.

  That is fundamental PROC REPORT processing. Until you fix that and the compound name issue, I suspect nothing will work as you expect.

cynthia

[HabAM]

Thank you both for the response. I have attached a sample dataset. I have tried it both ways and it didn't work.

Any suggestion?

[Cynthia_sas]

Sorry, you sent example.xlsx and the data that proc report is using is work.has. Also, you did not send ALL your proc report code. Aren't there some statements missing in the code you posted? There are NOT any DEFINE statements in the PROC REPORT code, at all??
cynthia

[HabAM]

Thank you Cythia.

I was not posting my dataset because I work in senstive area (with personal identifiable info). So what I did is replace all var with a generic name to post here. I have no issue with reading table, or issue with names of the dataset. I also didn't use Define statements on my code.

It actually works when I remove the and/or in the If statement.

what I just realize it isn't reading value from the table (NOTE: Variable value is uninitialized.)

[HabAM]

Thank you for the feedback. I actually have >= on my actuall code, removed it accidentally when I changed the var names.  I tried it with your suggestion (event though I don't understand the difference with mine) and it didn't yield the desired outcome. 

Any suggestion? I have added a sample data below.

Regards,

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

You can de-sensitize your data for an example set by 1) dropping variables not actually used for the Proc Report 2) changing values such as "John smith" to "Patient1" or "ABC Inc" to "Company3". You only need to provide enough observations to exercise the features you are using, group variables should have at least 2 levels for instance so that the grouping actually is noticeable (or fails). Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn a SAS dataset into data step code that can be pasted into the forum or attached as a text file so we can work with your data.

As a hint for calculations do not make all the numeric values 1, 0 or missing. Sometimes problems don't always appear so minimal variety such as 1 ,2,3 work better.

Also if you can show what the desired result for the example data should be it is very much more likely to get something closer to what you want.

[Cynthia_sas · Accepted solution]

Hi:

   This is some sample code I use with my students to illustrate these PROC REPORT concepts. The WRONG code is titled (#1 and #4) and the RIGHT code is titled (#2, #3 and #5) The programs all use SASHELP.CLASS and the highlighting is very straightforward, you should be able to backtrack from the highlighting in the numbered report to EXACTLY which code performed the highlighting. The programs illustrate the use of compound name and how it is tied to the usage on the DEFINE statement and also illustrate the left-to-right rule of PROC REPORT.

  I hope this helps.

cynthia

proc report data=sashelp.class;
title '1) WRONG: compute block does not use compound name';
column name age height weight;
define name / order;
define age / 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute height;
  if age le 12 then call define(_col_,'style','style={background=red}');
endcomp;
run;

proc report data=sashelp.class;
title '2) RIGHT: compute block does use compound name because default statistics for age is SUM';
column name age height weight;
define name / order;
define age / 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute height;
  if age.sum le 12 then call define(_col_,'style','style={background=red}');
endcomp;
run;

proc report data=sashelp.class;
title '3) RIGHT: compute block works because usage of AGE is DISPLAY';
column name age height weight;
define name / order;
define age / display 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute height;
  if age le 12 then call define(_col_,'style','style={background=red}');
endcomp;
run;


proc report data=sashelp.class;
title '4) WRONG: cannot test height/weight in compute block for AGE';
column name age height weight;
define name / order;
define age / display 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute age;
  if height.sum le 58 then do;
     call define('height.sum','style','style={background=yellow}');
	 call define(_col_,'style','style={background=yellow}');
  end;
  if weight.sum ge 115 then  call define('weight.sum','style','style={background=red}');
endcomp;
run;

proc report data=sashelp.class;
title '5) RIGHT: can test height in compute block for HEIGHT';
title2 'can only test weight in compute block for weight';
title3 'can test any vars to left of last var in column -- see all tests in WEIGHT COMPUTE7j';
column name age height weight;
define name / order;
define age / display 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute height;
  if height.sum le 58 then do;
     call define(_col_,'style','style={background=yellow}');
	 call define('age','style','style={background=yellow}');
  end;
endcomp;
compute weight;
  if weight.sum ge 115 then  call define(_col_,'style','style={background=red}');
  if name = 'Alice' then call define('name','style','style={background=pink}');
  if name = 'Barbara' then call define('name','style','style={background=lightgreen}');
  if age le 12 then call define('age','style','style={background=peachpuff}');
endcomp;
run;

[HabAM]

Thanks much