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
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.
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;
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;
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
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 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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.