BookmarkSubscribeRSS Feed
CharlesFowler72
Obsidian | Level 7

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 1Risk Level 1Risk Level 2Risk Level 2Risk Level 3Risk Level 3
SectorCurrent MonthPrevious MonthCurrent MonthPrevious MonthCurrent MonthPrevious Month
Sector 1613,82455,498457,137687,889817,745747,244
Sector 2268,295644,344592,035983,231179,525875,174
Sector 3184,796557,766190,897705,149984,726524,934
Sector 4112,054117,79037,873483,057723,435858,327
Sector 5342,069995,892480,019158,641597,644467,956
Sector 6389,570398,725330,18653,723296,942997,574
Total1,910,6062,770,0162,088,1463,071,6903,600,0154,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?

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
CharlesFowler72
Obsidian | Level 7
I'm not sure I follow how that works?
ballardw
Super User

@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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3 replies
  • 803 views
  • 2 likes
  • 3 in conversation