Computing Turnover - data attached

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Computing Turnover - data attached

Hello All,

I have a question about computing turnover, and I searched for the word "turnover" in the boards and couldn't find anything, so hopefully this will help some other people out in the future as well.

I have attached some data to work with, but in general, I have data that takes the following form:

Year                    Name         

2000                    JOHN

2000                    ALEX

2000                    FRANK

2000                    KARLEEN

2000                    BRITTNEY

2000                    JULIE

2000                    NOELLE

2001                    ALEX

2001                    JOHN

2001                    KARLEEN

2001                    FRANK

2001                    BRITTNEY   

2001                    JULIE

2001                    DANIELLE

I want to compute the turnover for this group of people.  In the year 2000, there were 7 people in the group.  In the year 2001, there were 7 people in the group, but "NOELLE" is no longer there and she has been replaced by "DANIELLE"  So my turnover would be 1/7 for the year 2001, since I replaced one out of 7 people.

I have to compare year to year inclusion in the group by comparing strings, since all of the data is character data.

I am working with stock data, so I have included a small sample of the data for you to work with.  There is data for each month showing the companies that classify themselves as "health care" companies.  I would like to know the turnover each month within this "health care" universe.  The identifiers are the "c_ticker" variables.

Also, note that there may not be the same number of obervations per month for the "health care" classification.  That adds a small amount of complexity.

Thanks very much!

John

Attachment

Accepted Solutions
Solution
‎07-07-2014 03:42 PM
Super User
Posts: 17,731

Re: Computing Turnover - data attached

I haven't fully tested this, but it makes sense in my head Smiley Happy

Basically, join the data to the next month data and if the data is present create a flag, 0 if its missing and 1 if its present, and then summarize in the next step.

proc sql;

create table turnover3 as

select a.c_ticker, a.month, case when missing(b.c_ticker) then 0

                                else 1 end as turnover

from turnover2 as a

left join turnover2 as b

on a.month=intnx('month', b.month, -1)

and a.c_ticker=b.c_ticker;

quit;

proc sql;

    create table summary as

    select a.month, count(*) as num_symbols, sum(turnover) as stayed, sum(turnover=0) as left

    from turnover3 a

    group by a.month;

quit;

EDIT: I did this looking forward, but you can change the join condition (a.month=intnx('month', b.month, -1)) to look at the previous month or whatever month you're interested in.

View solution in original post


All Replies
PROC Star
Posts: 1,086

Re: Computing Turnover - data attached

What about cases where existing people leave, and new people arrive?

For example, what would be the calculations in these cases?

2000                    JOHN
2000                    ALEX
2000                    FRANK
2001                    ALEX
2001                    JOHN

2000                    JOHN
2000                    ALEX
2001                    ALEX
2001                    JOHN
2001                    DANIELLE

Frequent Contributor
Posts: 101

Re: Computing Turnover - data attached

, thanks for your question and I should have made this more clear.

In both cases, there has been a change.  Meaning that something that was there in 2000 is no longer there in 2001 (case 1) or somethnig that was not there in 2000, is now there is 2001 (case 2).  I would be inclined to say that in case 1, the turnover would be 1/3, being that there was a "one unit" change when we originally had "three units".  Similarly for the second case, I would say that the value woudl be 1/2, for the same reasoning.


However, I am more interested in the absolute value of the change.  Namely, something like this for case 1:

2001: I have 2 names now, I had three names last period, and both names that I have now were present last period.

For case 2:

I have three names now, I had two names last period, and two out of the three names that I have now were present last period.

I hope this makes more sense.

John

Frequent Contributor
Posts: 101

Re: Computing Turnover - data attached

To make things a little easier,

All I want to know is if a name that is present in the current period, was present in the prior period.  I can do the rest of the weighting and summary work myself.

I just need to match the current list of names with the prior period list of names.

Any help is greatly appreciated!

John

Solution
‎07-07-2014 03:42 PM
Super User
Posts: 17,731

Re: Computing Turnover - data attached

I haven't fully tested this, but it makes sense in my head Smiley Happy

Basically, join the data to the next month data and if the data is present create a flag, 0 if its missing and 1 if its present, and then summarize in the next step.

proc sql;

create table turnover3 as

select a.c_ticker, a.month, case when missing(b.c_ticker) then 0

                                else 1 end as turnover

from turnover2 as a

left join turnover2 as b

on a.month=intnx('month', b.month, -1)

and a.c_ticker=b.c_ticker;

quit;

proc sql;

    create table summary as

    select a.month, count(*) as num_symbols, sum(turnover) as stayed, sum(turnover=0) as left

    from turnover3 a

    group by a.month;

quit;

EDIT: I did this looking forward, but you can change the join condition (a.month=intnx('month', b.month, -1)) to look at the previous month or whatever month you're interested in.

Frequent Contributor
Posts: 101

Re: Computing Turnover - data attached

You guys never cease to impress!  Thank you so much and everyone else!

Worked like a charm, and very elegantly solved.

John

Trusted Advisor
Posts: 1,204

Re: Computing Turnover - data attached

This will give names from year 2001 that were also in year 2000

data have;

input Year  Name $;

datalines;

2000                    JOHN

2000                    ALEX

2000                    FRANK

2000                    KARLEEN

2000                    BRITTNEY

2000                    JULIE

2000                    NOELLE

2001                    ALEX

2001                    JOHN

2001                    KARLEEN

2001                    FRANK

2001                    BRITTNEY

2001                    JULIE

2001                    DANIELLE

;

proc sql;

select year,name

from have

where year=2001

and name in (select name from have where year=2000);

quit;

Super User
Posts: 9,662

Re: Computing Turnover - data attached

If your data is large , I think Array might be a good tool.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 352 views
  • 3 likes
  • 5 in conversation