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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.