topic Re: Computing Turnover - data attached in SAS Programming

Computing Turnover - data attached

mahler_ji — Mon, 07 Jul 2014 15:48:40 GMT

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

Re: Computing Turnover - data attached

TomKari — Mon, 07 Jul 2014 16:24:20 GMT

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

Re: Computing Turnover - data attached

mahler_ji — Mon, 07 Jul 2014 16:42:53 GMT

, 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

Re: Computing Turnover - data attached

mahler_ji — Mon, 07 Jul 2014 19:31:18 GMT

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

Re: Computing Turnover - data attached

Reeza — Mon, 07 Jul 2014 19:42:36 GMT

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

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.

Re: Computing Turnover - data attached

stat_sas — Mon, 07 Jul 2014 19:48:22 GMT

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;

Re: Computing Turnover - data attached

mahler_ji — Mon, 07 Jul 2014 21:33:02 GMT

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

Worked like a charm, and very elegantly solved.

John

Re: Computing Turnover - data attached

Ksharp — Tue, 08 Jul 2014 14:44:28 GMT

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