Compute Blocks Revealed

1. Compute Blocks Revealed Sandy McNeill

2. Agenda • Uses of Compute Blocks • What do Compute Blocks look like? • Rules • Tricks/Traps • Questions

3. What are the uses of Compute Blocks?

4. Compute Block for Computed Variable

5. Compute Block for Traffic Lighting

6. Compute Block for Line Statements

7. Compute Block Tied to Break

8. What do Compute Blocks look like?

9. define balance / computed ; compute balance; balance=budget-actual; endcomp; compute before; totalaccts = 0; endcomp; As Simple As…. OR

10. As Cool As…. compute yrtodate; yrtodate=budget.sum-actual.sum; if yrtodate > 0 then call define( _col_, 'style', 'style={background=yellow}'); endcomp;

11. compute after dept; pctbud = round((budget.sum/totalbud)*100); updept = upcase(dept); line pctbud 2. '% of YTD budget allocated to ' updept $varying. len '.'; if yrtodate < 0 then do; pctover = abs(round((yrtodate/budget.sum)*100)); text2 = ‘Over budget by ' || trim(left(put(pctover,3.0))) || '%.'; end; else text2=' '; line text2 $80.; endcomp; As Complicated As…..

12. Rules of Operation

13. Evaluation / Execution 1 2 3

14. Values to the Left Column 3 is a computed column Can’t use COL4 value in COL3 BUT….. You can write to columns to the left

15. Analysis Variable “Dot” Syntax Weight.sum Height.median Weight.min Weight.max

16. Where can you use a Compute Block? • Before / After report • Compute before; • Compute after; • For any report variable • Compute weight; /* analysis variable */ • Compute age; /* group or order variable */ • Compute balance; /* computed variable */

17. Where or With What Variable (cont) • Before / After a grouping • Compute before age; • Compute after age; • Before / After Page or Table • Compute before _page_; • Compute after _page_;

18. Four Meanings of Analysis Vars • Compute before/after _page_ • Detail lines • Compute before/after Grouping • Compute before/after (report)

19. Compute Before / After _PAGE_ Compute Before _page_; Line ‘Budget.sum is ‘ budget.sum dollar12.2; Endcomp; Output looks like: Budget.sum is $40,000.00

20. Compute Before / After _PAGE_ Compute Before _page_; Line ‘Budget.sum is ‘ budget.sum dollar12.2; Endcomp; Output looks like: Budget.sum is $40,000.00 Where does REPORT get the value for Budget.sum ?

21. Answer: The value for budget.sum in the first detail line

22. Detail Lines Detail 1 Detail 2 Detail 3 Summary Compute Computed; Computed = Analysis.sum; Endcomp;

23. Detail Lines Detail 1 Detail 2 Detail 3 Summary Compute Computed; Computed = Analysis.sum; Endcomp; Where does REPORT get the value for Analysis.sum ?

24. Detail Lines Answer: From the value of Analysis.sum on the same detail line. Detail 1 Detail 2 Detail 3 Summary Compute Computed; Computed = Analysis.sum; Endcomp;

25. Compute before/after Grouping Detail 1 Detail 2 Compute After Group1 Break after group1 /; Compute after Group1; Line ‘Compute After Group1. Val = ‘ analysis.sum 5.; Endcomp;

26. Compute before/after Grouping Detail 1 Detail 2 Compute After Group1 Break after group1 /; Compute after Group1; Line ‘Compute After Group1. Val = ‘ analysis.sum 5.; Endcomp; Where does REPORT get the value for Analysis.sum ?

27. Compute before/after Grouping Answer: From the summarized value of Analysis.sum for this grouping. NOTE: Summary is implicitly done by REPORT. Detail 1 Detail 2 Compute After Group1 Break after group1 /; Compute after Group1; Line ‘Compute After Group1. Val = ‘ analysis.sum 5.; Endcomp;

29. Compute after ; Detail 1 Detail 2 Detail 3 Report Summary RBREAK after /; Compute after; Line ‘Line Stmt after Report. Val = ‘ analysis.sum 5.; Endcomp;

30. Compute after ; Detail 1 Detail 2 Detail 3 Report Summary RBREAK after /; Compute after; Line ‘Line Stmt after Report. Val = ‘ analysis.sum 5.; Endcomp; Where does REPORT get the value for Analysis.sum ?

31. Compute after ; Answer: From the summarized value of Analysis.sum for this report. NOTE: Summary is implicitly done by REPORT. Detail 1 Detail 2 Detail 3 Report Summary RBREAK after /; Compute after; Line ‘Line Stmt after Report. Val = ‘ analysis.sum 5.; Endcomp;

32. Traps and Tricks

33. “Dot” Syntax Analysis Variable . Statistic

34. Computed Columns Under Across Column dept quarter,(sales actual balance); Define balance / computed; Compute Balance; _c4_ = _c2_ - _c3_; _c7_ = _c5_ - _c6_; Endcomp;

35. Performance suggestion/trick • Sometimes it is best to create a dummy column and do your work in that column • Have the dummy column compute block write the values in the cells that are to its left • To make this work, the dummy column must come to the RIGHT of any column values you want to use • This dummy column is usually not printed – make it a NOPRINT column

36. Computed Columns Under Across Column dept quarter,(sales actual balance) dummy; Define balance / computed; Define dummy / noprint; Compute Dummy; _c4_ = _c2_ - _c3_; _c7_ = _c5_ - _c6_; Endcomp;

37. Computed Columns Don’t Summarize Detail 1 Detail 2 Detail 3 Summary Compute computedColumn; computedColumn = 5; Endcomp; Rbreak after / summarize;

38. Computed Columns Don’t Summarize Detail 1 Detail 2 Detail 3 Summary Compute computedColumn; If _break_ = ‘ ‘ then do; ComputedColumn = 5; total = total + ComputedColumn; End; Else computedColumn = total; Endcomp;

39. Summary • Uses of Compute Blocks • What do Compute Blocks look like? • Rules • Tricks/Traps

40. Questions ?