Calculate average monthly change for past 12 months

Reply
Frequent Contributor
Posts: 75

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. 

 

Can you please help? 

Grand Advisor
Posts: 17,464

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: 75

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

Grand Advisor
Posts: 10,251

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: 75

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?)

Grand Advisor
Posts: 17,464

Re: Calculate average monthly change for past 12 months

Based on your example data it appears correct

Frequent Contributor
Posts: 75

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? 

Grand Advisor
Posts: 17,464

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: 75

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

Grand Advisor
Posts: 17,464

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: 75

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?

Grand Advisor
Posts: 17,464

Re: Calculate average monthly change for past 12 months

Lag2

Frequent Contributor
Posts: 75

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

Grand Advisor
Posts: 10,251

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: 75

Re: Calculate average monthly change for past 12 months

feb 2013 - jan 2013

Ask a Question
Discussion stats
  • 14 replies
  • 610 views
  • 0 likes
  • 3 in conversation