DATA Step, Macro, Functions and more

Record the evolution of individuals

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Record the evolution of individuals

Hello,

 

 

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

 

Year 1:

Level       ID

 

L1 ECO210WEAO
L1 A211BWBC
L1 B210ECFP
L1 C211PKKJ
L1 D211HVHQ
L1 E211POMB
L1 F211ZNQQ
L1 G210AMJL
L1 H209XBZI

 

Year 2

 

Level       ID

 

L2 ECO210WEAO
L2 A211BWBC
L1 B210ECFP
L1 C211PKKJ
L2 D211HVHQ
L1 E211POMB
L1 F211ZNQQ
L2 G210AMJL
  

 

Year 3

 

Level       ID

 

L3 ECO210WEAO
L2 A211BWBC
L2 B210ECFP
L2 C211PKKJ
L2 D211HVHQ
L1 E211POMB
L2 F211ZNQQ
L3 G210AMJL
  

 

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

 

IDSameUpdeletechange
210WEAO0100
211BWBC0100
210ECFP0000
211PKKJ0000
211HVHQ0100
211POMB1000
211ZNQQ0001
210AMJL0100
209XBZI0010

 

 

 


Thanks for your help


Accepted Solutions
Solution
‎02-01-2018 04:59 PM
PROC Star
Posts: 1,308

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. 

View solution in original post


All Replies
Frequent Contributor
Posts: 109

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;
PROC Star
Posts: 1,308

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 ECO210WEAO L2 ECO210WEAO L3 ECO210WEAO
L1 A211BWBC L2 A211BWBC L2 A211BWBC
L1 B210ECFP L1 B210ECFP L2 B210ECFP
L1 C211PKKJ L1 C211PKKJ L2 C211PKKJ
L1 D211HVHQ L2 D211HVHQ L2 D211HVHQ
L1 E211POMB L1 E211POMB L1 E211POMB
L1 F211ZNQQ L1 Wi211ZNQQ L2 Wi211ZNQQ
L1 G210AMJL L2 G210AMJL L3 G210AMJL
L1 H209XBZI      

 

 

I want to get this

 

IDSameUpdeletechange
210WEAO0100
211BWBC0100
210ECFP0000
211PKKJ0000
211HVHQ0100
211POMB1000
211ZNQQ0001
210AMJL0100
209XBZI0010

 

 

 


Thanks for your help

Solution
‎02-01-2018 04:59 PM
PROC Star
Posts: 1,308

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.
PROC Star
Posts: 1,308

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 nowSmiley Happy Have a good afternoon

☑ This topic is solved.

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

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