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
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.
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
, 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
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
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.
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;
If your data is large , I think Array might be a good tool.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.