BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
Hi:
Thanks for posting your code. All of my comments are still relevant. You CANNOT test both AMTPRE and AMTPRE_1096 in the COMPUTE block for AMTPRE because of the the "left to right" rule of PROC REPORT.

cynthia

View solution in original post

14 REPLIES 14
Kurt_Bremser
Super User

Depending on how your variables are calculated, you might run into numeric precision issues that cause differences where there should be none.

Use the round() function to remove any artifacts that hide far behind the decimal comma.

For better readability, I would also replace "not eq" with the simpler mnemonic "ne".

srinidelite
Obsidian | Level 7

Thanks Kurt  🙂

 

However both the suggestions has no effect on the report !

 

COMPUTE AMTPRE;
 IF round(AMTPRE,.02) ne round(AMTPRE_1096 ,.02)
THEN DO ;
 call define (_col_,"style","style={background= orange}");
 END;
 ENDCOMP;

 

srinidelite
Obsidian | Level 7
im certainly thinking it highlights the column completely however it should be a cell to be highlighted
Cynthia_sas
SAS Super FREQ

Hi:
How are AMTPRE and AMTPRE96 (and other variables) used on the report. By that I mean are you specifically listing them in a DEFINE statement with a usage of DISPLAY? Or are you taking the default usage of SUM?

What does your COLUMN statement look like? For example, you need to remember the "left to right" rule of PROC REPORT. PROC REPORT does not work like the DATA step. There is NO Program Data Vector. PROC REPORT can only "see" what has been placed on the report row, working from left to right.

Here is an example of what I mean:
proc report data=fakedata;
  column var1 var2 var3 var4;
  define var1 / display;
  define var2 / display;
  define var3 / sum;
  define var4 / sum;
  compute var1;
    if var1 ne var2 then do;
      call define (....);
    end;
  endcomp;
  compute var4;
    if var3.sum ne var4.sum then do;
       call define (....);
    end;
  endcomp;
run;

The COMPUTE block for VAR1 is WRONG because of the left to right rule. At the point in time when PROC REPORT is writing VAR1 on the report, it has not yet written VAR2, so even though the comparison in the IF is correct for DISPLAY variables, the result will ALWAYS be false because at this point in time even if VAR1 has a value, VAR2 is missing on the report row because it hasn't been written yet. So the IF will ALWAYS be true.

As shown in the COMPUTE block for VAR4, you can test any item on the REPORT row, that is to the left of VAR4. So, although a COMPUTE block for VAR3 could NOT test VAR4, a COMPUTE block for VAR4 could test VAR3.

PROC REPORT ALWAYS moves from left to right, placing items on the report row one by one. So it is a timing and placement issue.

Then there are just a few other things to remember: for numeric variables that are NOT GROUP, ORDER or DISPLAY usage, you MUST use the construct: variable_name.statistic (as in VAR3.sum or VAR4.sum) and the syntax in the call define will need to be different. If you want to "touch" VAR3 in the COMPUTE block for VAR4, then the CALL DEFINE has to be like this:
compute var4;
  if var3.sum ne var4.sum then do;
    call define ('var3.sum','style','style={background=orange}');
  end;
endcomp;

Notice how instead of _COL_, you need to use the item reference in quotes if you want to impact an item that is to the left of the item in the COMPUTE block.

cynthia

srinidelite
Obsidian | Level 7

dekle

Cynthia_sas
SAS Super FREQ
Hi:
Thanks for posting your code. All of my comments are still relevant. You CANNOT test both AMTPRE and AMTPRE_1096 in the COMPUTE block for AMTPRE because of the the "left to right" rule of PROC REPORT.

cynthia
srinidelite
Obsidian | Level 7

I Understand it works on Left to Right .. However if i want to compare my right with Left again , What i suppose to do ?

 

Pls suggest me the Changes 

Kurt_Bremser
Super User

It seems to depend on the existence of a column. Computing a new one does the trick:

data test;
x1 = 1;
x2 = 2;
output;
x1 = 1;
x2 = 1;
output;
run;

proc report data=test;
column x1 x2 x3;
define x1 / display noprint;
define x2 / display;
compute x3;
  if x1 ne x2
  then call define(_col_,"style","style={background=orange}");
  x3 = x1;
endcomp;
run;
Cynthia_sas
SAS Super FREQ

Hi:
You can always "impact" x1 and x2 in the COMPUTE block for x3 because at the point in time when X3 is being placed on the report, PROC REPORT has full visibility of X1 and X2. Your code would also have worked if you did this:

data test;
  infile datalines;
  input x1 x2 x3 x4;
return;
datalines;
1 2 3 4
1 1 4 4
5 6 7 7
;
run;

proc report data=test;
column x1 x2 x3 x4;
define x1 / display;
define x2 / display;
define x3 / sum;
define x4 / sum;
compute x2;
  if x1 ne x2
    then call define(_col_,"style","style={background=orange}");
endcomp;
compute x4;
  if x3.sum ne x4.sum
    then call define('x3.sum',"style","style={background=orange}");
endcomp;
run;


cynthia

Kurt_Bremser
Super User

I think I get it. But is it possible to let the variables appear in the sequence x1 x2 and set the style for x1 depending on x2 (or a comparison of x2 and x1)?

Cynthia_sas
SAS Super FREQ
Hi, Kurt, yes it is. Look at my compute block for X4, it is altering the style for X3 in the CALL DEFINE.

In PROC REPORT, you can test and/or change the style of the items on the report row, but since PROC REPORT puts the items down working from left to right, you can only compare X3 and X4 in the COMPUTE block for X4 and in the compute block for X4, you can "touch" the style of X3, as I show.

cynthia
Ksharp
Super User
 Make sure AMTPRE is after AMTPRE96  .   AMTEXTFORPAYPERI  is after AMTE4PAPE96  
srinidelite
Obsidian | Level 7
Thanks you .. It works Now ,,
BrunoMueller
SAS Super FREQ

In general when you want to do something like this, I recommend to follow these principles:

 

All analysis variable have an alias (varname=alias), this allows to use the same var more than once, for instance because you need more than one statistic. Also it has the advantage, that you can use the alias name whereever you need this variable name: DEFINE statement, CALL DEFINE routine, assignment statement etc.

 

Add a computed column at the very end of your COLUMN statement and set the NOPRINT option in the DEFINE statement. You can now code everything you need in the compute block of this computed column. Since it is at the end of the column list, it can access all the variable values to the left.

 

The code below illustrates this.

data mycars;
  set sashelp.cars(obs=10);
  if mod(_n_, 3) then do;
    msrp = invoice;
  end;
run;

proc report data=myCars;
  column make model invoice=inv2 msrp=msrp2 _dummy;
  define make / display;
  define model / display;
  define inv2 / analysis sum;
  define msrp2 / analysis sum;
  define _dummy / computed noprint;

  compute _dummy;
    if msrp2 > inv2 then do;
      call define("msrp2", "style", "style={background=gold}");
      call define("inv2", "style", "style={background=cx88e55c}");
    end;
  endcomp;
run;

Bruno

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2685 views
  • 12 likes
  • 5 in conversation