turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Calculate average monthly change for past 12 month...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 10:43 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 10:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 11:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 11:18 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 11:23 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 11:37 AM

Based on your example data it appears correct

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 11:48 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 11:53 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 11:56 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 12:02 PM

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 12:12 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 12:18 PM

Lag2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 12:27 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 01:45 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2016 01:56 PM

feb 2013 - jan 2013