## Calculate average monthly change for past 12 months

Frequent Contributor
Posts: 79

# Calculate average monthly change for past 12 months

I have a datset with 12 months (SDATE) and I want to calcuate the average change from month-to-month in several variables (total number of employees, for example). There is a weight FINALWT.

Super User
Posts: 23,700

## Re: Calculate average monthly change for past 12 months

Please post some sample data and expected output. Ideally the calculations/numbers should reflect what you're looking for in your output.

Frequent Contributor
Posts: 79

## Re: Calculate average monthly change for past 12 months

SDATE      Employed

201501          1

201501          1

201501          0

201501          0

201502          1

201502          1

201502          1

201502          0

201503          1

...

Want output step 1:

201501 total employed = 2

201502 total employed = 3

201503 total employed = 1

Want output step2:

Average monthly change (201501+201502+201503)/num of months = (2+3+1)/3 = 2

Super User
Posts: 13,523

## Re: Calculate average monthly change for past 12 months

Something like this perhaps:

``````proc summary data=have nway;
class sdate;
var employed;
output out=temp sum=;
run;

proc means data=temp mean;
var employed;
run;``````
Frequent Contributor
Posts: 79

## Re: Calculate average monthly change for past 12 months

Thanks but wouldn't this output the average employment level (rather than the average change between months?)

Super User
Posts: 23,700

## Re: Calculate average monthly change for past 12 months

Based on your example data it appears correct

Frequent Contributor
Posts: 79

## Re: Calculate average monthly change for past 12 months

just tried it; definitely just calculates average employment level.

let me put it another way: how can I calculate the difference between the sum of employment for 201502 from 201503?

Super User
Posts: 23,700

## Re: Calculate average monthly change for past 12 months

fieldsa83 wrote:

SDATE      Employed

201501          1

201501          1

201501          0

201501          0

201502          1

201502          1

201502          1

201502          0

201503          1

...

Want output step 1:

201501 total employed = 2

201502 total employed = 3

201503 total employed = 1

Want output step2:

Average monthly change (201501+201502+201503)/num of months = (2+3+1)/3 = 2

Proc means is reporting the 2 value you requested here.

What value are you expecting?

Frequent Contributor
Posts: 79

## Re: Calculate average monthly change for past 12 months

Let me change what step 1 needs to be:

How can I calculate the sum of employment for 201502 MINUS the sum of employment for 201501?

Which would be 3-2=1 in this example

Super User
Posts: 23,700

## Re: Calculate average monthly change for past 12 months

``````proc summary data=have nway;
class sdate;
var employed;
output out=temp sum=;
run;

data want;
set temp;
prev_month = lag(employed);
diff = employed - prev_month;
run;``````

Frequent Contributor
Posts: 79

## Re: Calculate average monthly change for past 12 months

ok perfect, and is there a way to use lag to get the change compared with 2 months earlier? so 201503-201501?

Super User
Posts: 23,700

## Re: Calculate average monthly change for past 12 months

Lag2

Frequent Contributor
Posts: 79

## Re: Calculate average monthly change for past 12 months

ok so here's where it gets more complicated.

I don't have 12 months in a row; instead, they are the same 2 months over 6 years. The idea is to get an aprox seasonal pattern.

Jan 2011

Feb 2011

Jan 2012

Feb 2012

Jan 2013

Feb 2013

...

Now I want to

1. calculate the difference between Feb 2011 and Jan 2011 = x

2. " " Feb 2012 and Jan 2012 = y

3. " " Feb 2013 and Feb 2013 = z

Then i want to

4. Find the average of the change between those two months (average of x,y,z)

Note: I do not actually need the x,y,z variables in the final table

Super User
Posts: 13,523

## Re: Calculate average monthly change for past 12 months

fieldsa83 wrote:

Now I want to

1. calculate the difference between Feb 2011 and Jan 2011 = x

2. " " Feb 2012 and Jan 2012 = y

3. " " Feb 2013 and Feb 2013 = z

Red part will likely always be zero. Did you mean Feb and Jan 2013 or Feb 2012 and Feb 2013?

Frequent Contributor
Posts: 79

## Re: Calculate average monthly change for past 12 months

feb 2013 - jan 2013

Discussion stats
• 14 replies
• 707 views
• 0 likes
• 3 in conversation