BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mahler_ji
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
TomKari
Onyx | Level 15

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

mahler_ji
Obsidian | Level 7

, 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

mahler_ji
Obsidian | Level 7

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

Reeza
Super User

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.

mahler_ji
Obsidian | Level 7

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

Worked like a charm, and very elegantly solved.

John

stat_sas
Ammonite | Level 13

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;

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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