tracking observations

[trich12]

I have data with some observations which move from group 1 to group 2 and some from group 2 to group 1 over different years. I want to track these observations to get a count of how many actually move from group to group, and which way they've moved. Does anyone know a procedure to do this?

[sbb]

Please explain how "observations which move from...to..." - is there a master file that is updated in-place or is there some type of cycle / version type of aging going on with different SAS files for a particular period or year/month?

And, also, how does one observation expect to be identified for tracking purposes, with some type of "key variable" that does not change over time?

You will need to explain more accurately just how the data/information is processed input/output, or whether it's being maintained as a master file.

At a minimum, it would be possible to maintain a prior cycle/version "copy" and use PROC COMPARE, based on some "key variable" identification, in order to compare variable value changes over time.

Scott Barry
SBBWorks, Inc.

[ChrisNZ]

You have to think this out a bit more.

Over how many years? If an observation goes from 1 to 2 to 1 to 1 to 2 (only 2 groups?) in five years, how do you want to see it? Number of jumps? Number of jumps up? Give it a label called '1-2-1-2' ? or '1-2-1-1-2'? Only look at start and end positions, so '1-2'? You have to know exactly the result you want before you search how to achieve it.

[trich12]

Thanks for your comments. Let me further explain my data. The dataset includes executive names, their positions, and their companies over a 12 year period. I've already isolated the executives that moved from one company to another during the period, but now I'm wanting to distinguish those who changed positions when they moved. For example, I need to see how many went from being CEO of one company to the CFO of another. There is a separate numerical code for each executive and each company, but the positions are only described in words, so I've created a dummy variable for each position. I just can't figure out how to track the observations that changed positions when they changed companies. I hope this helps explain what I'm trying to accomplish and what my dataset looks like. Any suggestions are appreciated!

[Patrick]

Hi

It would always help if you provide some example data in form of a data step and then show how the result should look like.

Have a look at below example code. May be this will give you some ideas.

data have;
input Year:date9. CompanyCD ExecCD PositionCD;
format year year4.;
datalines;
01jan2004 1 1 10
01jan2005 1 1 10
01jan2006 2 1 10
01jan2007 2 1 10
01jan2008 3 1 20
01jan2009 3 1 20
01jan2010 3 1 30
01jan2011 3 1 30
;
run;

proc sort data=have;
by ExecCD Year;
run;

proc format;
value $changes
'00' = 'No Change'
'10' = 'Change Company'
'01' = 'Change Position'
'11' = 'Change Company and Position'
;
quit;

data want;
set have;
by ExecCD Year;
attrib ChangeFlags length=$2 format=$changes.;
ChangeFlags='00';

if not first.ExecCD and lag(CompanyCD) ne CompanyCD then substr(ChangeFlags,1,1)='1';

if not first.ExecCD and lag(PositionCD) ne PositionCD then substr(ChangeFlags,2,1)='1';

run;

proc print data=want;
run;


HTH
Patrick

[ChrisNZ]

Like this?
[pre]

data IN;
input PERIOD 1. NAME : $3. CO : $1. POS : $1. ;
cards;
1 Bob A a
1 Rob B b
1 Mat C c
2 Bob A a
2 Rob D b
2 Mat C c
3 Bob A a
3 Rob D b
3 Mat C d
4 Bob E d
4 Rob D b
4 Mat C d
run;
proc sql;
select a.NAME
,ifc(sum(a.POS ne b.POS and a.CO ne b.CO),'Y','N') as DIF_CO_AND_POS length=1
,ifc(sum(a.POS eq b.POS and a.CO ne b.CO),'Y','N') as DIF_CO_AND_NOTPOS length=1
,ifc(sum(a.POS ne b.POS and a.CO eq b.CO),'Y','N') as DIF_NOTCO_AND_POS length=1
,ifc(sum(a.POS='a' and b.POS='d' and a.CO ne b.CO),'Y','N') as DIF_CO_AND_POS_A_TO_D length=1
from IN a
,IN b
where a.NAME=b.NAME
and a.PERIOD
group by a.NAME;
quit;



DIF_CO_ DIF_CO_AND_ DIF_NOTCO_ DIF_CO_AND_
NAME AND_POS NOTPOS AND_POS POS_A_TO_D
___________________________________________________________
Bob Y N N Y
Mat N N Y N
Rob N Y N N