Proc Report Compute with multiple across variables

[CharlesFowler72]

Hi Guys,

im sure this is out there somewhere but cannot for the life of me find it. Maybe using the wrong key words to search. Anyway...

what im looking to do is add in a compute variable in a proc report to show percentage change between two months. I have multiple across variables, at the top there is risk level with options 1, 2 and 3. Then the next across variable is month which is either Current or Previous. I then need a compute variable to show the percentage change. There is also a grouped variable of sectors.

My current code is as follows...

proc report data=temp;
Title "Table 1.1";
Column sector risk_level , month,(cost);
define sector / group "Sector";
define risk_level / across "";
define month / across "";
define cost / analysis sum format=comma14. "";

rbreak after / dol skip summarize;
run;

which gives the below table as an output

	Risk Level 1	Risk Level 1	Risk Level 2	Risk Level 2	Risk Level 3	Risk Level 3
Sector	Current Month	Previous Month	Current Month	Previous Month	Current Month	Previous Month
Sector 1	613,824	55,498	457,137	687,889	817,745	747,244
Sector 2	268,295	644,344	592,035	983,231	179,525	875,174
Sector 3	184,796	557,766	190,897	705,149	984,726	524,934
Sector 4	112,054	117,790	37,873	483,057	723,435	858,327
Sector 5	342,069	995,892	480,019	158,641	597,644	467,956
Sector 6	389,570	398,725	330,186	53,723	296,942	997,574
Total	1,910,606	2,770,016	2,088,146	3,071,690	3,600,015	4,471,210

I have tried something similar to below

define change / computed;
compute change;
_c4_ = _c3_ - _C2_;
_c7_ = _c6_ - _C5_;
_c10_ = _c9_ - _C8_;
endcomp;

but always get an error.

Any ideas?

[PaigeMiller]

Make your life simple. Run PROC SUMMARY to get the totals. Do the math to compute percentages in a data step. Then use PROC REPORT to display the results.

[CharlesFowler72]

I'm not sure I follow how that works?

[ballardw]

---

@CharlesFowler72 wrote:
I'm not sure I follow how that works?

---

Here is an example of using proc summary to get totals by group variables and then calculation percentage of change.

proc summary data=sashelp.class nway;
   class sex age;
   var weight;
   output out=work.class sum=;
run;

data work.want;
   set work.class;
   by sex age;
   difweight= dif(weight);
   pctdifweight= difweight/weight;
   if first.sex then call missing(difweight,pctdifweight);
run;

The order of variables on the class statement makes a difference on what the percent calculated would be. In this case it is the difference of total weight between age within sex. If the order of the BY variables were reversed it would be difference between SEX within age (which probably makes more sense in most use terms but this is code example).

You have more variables that would be class variables, sector risk and month. So you would need to consider carefully which you want.

The DIF function returns the value of the current observation minus the previous observation and has multiple levels such as DIF10 which would be the difference from the current to the 10th previous observation. However since you have groups that likely don't want to calculated across care needs to be taken to get the correct calculations.

READ the documentation on LAG and DIF and why use of the functions within an IF statement often does not do what you expect. Better is to calculate unconditionally and then use values as needed.