topic Re: Record the evolution of individuals in SAS Programming

Record the evolution of individuals

WilliamB — Wed, 31 Jan 2018 16:11:00 GMT

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

Re: Record the evolution of individuals

Satish_Parida — Wed, 31 Jan 2018 17:08:22 GMT

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;

Re: Record the evolution of individuals

novinosrin — Wed, 31 Jan 2018 18:08:15 GMT

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;

Re: Record the evolution of individuals

WilliamB — Thu, 01 Feb 2018 21:07:11 GMT

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

Re: Record the evolution of individuals

novinosrin — Thu, 01 Feb 2018 21:41:14 GMT

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.

Re: Record the evolution of individuals

WilliamB — Thu, 01 Feb 2018 21:59:52 GMT

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.

Re: Record the evolution of individuals

novinosrin — Thu, 01 Feb 2018 22:03:42 GMT

@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