Record the evolution of individuals

[WilliamB]

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

[Satish_Parida]

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;

[novinosrin]

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;




    

[WilliamB]

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

[novinosrin · Accepted solution]

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. 

[WilliamB]

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.

[novinosrin]

@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