Solved
Contributor
Posts: 33

# Record the evolution of individuals

Hello,

I have 3 tables representing the characteristics of individuals over 3 years.

Year 1:

Level       ID

 L1 ECO 210WEAO L1 A 211BWBC L1 B 210ECFP L1 C 211PKKJ L1 D 211HVHQ L1 E 211POMB L1 F 211ZNQQ L1 G 210AMJL L1 H 209XBZI

Year 2

Level       ID

 L2 ECO 210WEAO L2 A 211BWBC L1 B 210ECFP L1 C 211PKKJ L2 D 211HVHQ L1 E 211POMB L1 F 211ZNQQ L2 G 210AMJL

Year 3

Level       ID

 L3 ECO 210WEAO L2 A 211BWBC L2 B 210ECFP L2 C 211PKKJ L2 D 211HVHQ L1 E 211POMB L2 F 211ZNQQ L3 G 210AMJL

I want to create a table with the identifier of individuals, and 4 new binary variables.

The first: Same = 1 if the level is the same for the 3 tables (example 211POMB).

The second: Up = 1 if the second year the individual is in "L2" [use:
index (upcase (Level), 'L2')]

The third: Delete=1 IF the individual disappeared in year 2 (example 209XBZI)

The fourth: change=1  if the individual has changed group (example 211ZNQQ year 1 "L1 F "  and year "L1 Wi", but it is still L1 and not L2).

I want to get this

 ID Same Up delete change 210WEAO 0 1 0 0 211BWBC 0 1 0 0 210ECFP 0 0 0 0 211PKKJ 0 0 0 0 211HVHQ 0 1 0 0 211POMB 1 0 0 0 211ZNQQ 0 0 0 1 210AMJL 0 1 0 0 209XBZI 0 0 1 0

Thanks for your help

Accepted Solutions
Solution
‎02-01-2018 04:59 PM
Super User
Posts: 2,061

## Re: Record the evolution of individuals

I modified to what i understood, however I would sincerely request you to verify the logic thoroughly. See if this

change=((scan(level,1)=scan(_level2,1)) and (scan(_level3,2) ne scan(level,2)));

meets your change logic that you wrote in simple words.

Either way, of course I can make further changes if it requires.

``````data want;
if _n_=1 then do;
if 0 then do; set year2(rename=(level=_level2));set year3(rename=(level=_level3));end;
dcl hash h(dataset:'year2(rename=(level=_level2))', multidata: 'y');
h.definekey('id');
h.definedata('_level2');
h.definedone();
dcl hash h1(dataset:'year3(rename=(level=_level3))', multidata: 'y');
h1.definekey('id');
h1.definedata('_level3');
h1.definedone();
end;
set year1;
same=0;up=0;delete=0;change=0;
if h.find()=0 and h1.find()=0 then do;
same=level=_level2=_level3;
up=(index(upcase (_Level2), 'L2')>0) ;
change=((scan(level,1)=scan(_level2,1)) and (scan(_level3,2) ne scan(level,2)));
end;
Delete= h.check() ne 0;
drop _: level;
run;``````

I am a little wary of it.

All Replies
Frequent Contributor
Posts: 112

## Re: Record the evolution of individuals

[ Edited ]
``````data finale_sheet;
merge y1(in=a rename=(Level=Level1))
y2(in=b rename=(Level=Level2))
y3(in=c rename=(Level=Level3));
by id;
if Level1=Level2=Level3 then same=1;
else same=0;
if (input(substr(Level1,2,1),1.)<input(substr(Level2,2,1),1.)) or (input(substr(Level2,2,1),1.) < input(substr(Level1,2,1),1.)) then UP=1;
else up=0;
if Level2='' then delete=1;
else delete=0;
if (substr(Level1,4) ne substr(Level2,4)) or (substr(Level2,4) ne substr(Level3,4)) then change=1;
else change=0;
run;``````
Super User
Posts: 2,061

## Re: Record the evolution of individuals

Your change logic isn't clear to me and so I have commented out that part for you to modify or if you clarify,I'll modify for you.

``````data Year1;
input Level & \$8.     ID : \$10.;
datalines;
L1 ECO	210WEAO
L1 A	211BWBC
L1 B	210ECFP
L1 C	211PKKJ
L1 D	211HVHQ
L1 E	211POMB
L1 F	211ZNQQ
L1 G	210AMJL
L1 H	209XBZI
;

data year2;
input Level & \$8.     ID : \$10.;
datalines;
L2 ECO	210WEAO
L2 A	211BWBC
L1 B	210ECFP
L1 C	211PKKJ
L2 D	211HVHQ
L1 E	211POMB
L1 F	211ZNQQ
L2 G	210AMJL
;

data Year3;
input Level & \$8.     ID : \$10.;
datalines;
L3 ECO	210WEAO
L2 A	211BWBC
L2 B	210ECFP
L2 C	211PKKJ
L2 D	211HVHQ
L1 E	211POMB
L2 F	211ZNQQ
L3 G	210AMJL
;

data want;
if _n_=1 then do;
if 0 then do; set year2(rename=(level=_level2));set year3(rename=(level=_level3));end;
dcl hash h(dataset:'year2(rename=(level=_level2))', multidata: 'y');
h.definekey('id');
h.definedata('_level2');
h.definedone();
dcl hash h1(dataset:'year3(rename=(level=_level3))', multidata: 'y');
h1.definekey('id');
h1.definedata('_level3');
h1.definedone();
end;
set year1;
same=0;up=0;change=0;delete=0;
if h.find()=0 and h1.find()=0 then do;
same=level=_level2=_level3;
up=(index(upcase (_Level2), 'L2')>0) ;
*change= (scan(_level2,1) ne scan(_level3,1));
end;
Delete= h.check() ne 0;

drop _: level;
run;

``````
Contributor
Posts: 33

## Re: Record the evolution of individuals

[ Edited ]
Posted in reply to novinosrin

thank you so much.

Your solution is great !!!!

It's just the change variable, which does not work well.

But I was not very clear.

I made a mistake in my final table.

I would like the variable change = 1,
when the individual remains in L1 in years 2, but is not in the same L1,

ex for the individual 211ZNQQ, he is in L1 year 1 and year 2, but in year 1 he is in 'F' and year 2 in 'Wi', but he is still in L1

Year1                                         Year2                                                   Year3

Level             ID                     Level             ID                                      Level             ID

 L1 ECO 210WEAO L2 ECO 210WEAO L3 ECO 210WEAO L1 A 211BWBC L2 A 211BWBC L2 A 211BWBC L1 B 210ECFP L1 B 210ECFP L2 B 210ECFP L1 C 211PKKJ L1 C 211PKKJ L2 C 211PKKJ L1 D 211HVHQ L2 D 211HVHQ L2 D 211HVHQ L1 E 211POMB L1 E 211POMB L1 E 211POMB L1 F 211ZNQQ L1 Wi 211ZNQQ L2 Wi 211ZNQQ L1 G 210AMJL L2 G 210AMJL L3 G 210AMJL L1 H 209XBZI

I want to get this

 ID Same Up delete change 210WEAO 0 1 0 0 211BWBC 0 1 0 0 210ECFP 0 0 0 0 211PKKJ 0 0 0 0 211HVHQ 0 1 0 0 211POMB 1 0 0 0 211ZNQQ 0 0 0 1 210AMJL 0 1 0 0 209XBZI 0 0 1 0

Thanks for your help

Solution
‎02-01-2018 04:59 PM
Super User
Posts: 2,061

## Re: Record the evolution of individuals

I modified to what i understood, however I would sincerely request you to verify the logic thoroughly. See if this

change=((scan(level,1)=scan(_level2,1)) and (scan(_level3,2) ne scan(level,2)));

meets your change logic that you wrote in simple words.

Either way, of course I can make further changes if it requires.

``````data want;
if _n_=1 then do;
if 0 then do; set year2(rename=(level=_level2));set year3(rename=(level=_level3));end;
dcl hash h(dataset:'year2(rename=(level=_level2))', multidata: 'y');
h.definekey('id');
h.definedata('_level2');
h.definedone();
dcl hash h1(dataset:'year3(rename=(level=_level3))', multidata: 'y');
h1.definekey('id');
h1.definedata('_level3');
h1.definedone();
end;
set year1;
same=0;up=0;delete=0;change=0;
if h.find()=0 and h1.find()=0 then do;
same=level=_level2=_level3;
up=(index(upcase (_Level2), 'L2')>0) ;
change=((scan(level,1)=scan(_level2,1)) and (scan(_level3,2) ne scan(level,2)));
end;
Delete= h.check() ne 0;
drop _: level;
run;``````

I am a little wary of it.

Contributor
Posts: 33

## Re: Record the evolution of individuals

Posted in reply to novinosrin
Thank you, it's very nice to have helped me.

I'll be careful if you're suspicious of your code.

If necessary, I will call you for another problem, or complete this code.
Super User
Posts: 2,061

## Re: Record the evolution of individuals

@WilliamB Indeed, I'll have my eyes wide open and in my opinion the i'm afraid the thread is still open.  Chilax for now Have a good afternoon

☑ This topic is solved.

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

Discussion stats
• 6 replies
• 146 views
• 4 likes
• 3 in conversation