Dear Experts,
I have a large dataset with so many records, I want to fill the missing values. I to create new two variables, "Principal" and "Vice".
data school;
length schoolname $20 teachername $50;
input schoolname 1-21 subject$ 23 ID 26-27 teachername 32-54 Qtype;
cards;
holmes middle school M -1 Doris Wilson 5
holmes middle school M 0 Amy Smith 7
holmes middle school R 1 Mrs. A. T. Whitecotton 6
holmes middle school M 2 Geraldine Carpenter 6
holmes middle school R 3 Jennie Crigler 6
holmes middle school M 4 Lida Gray Gibbs 6
holmes middle school M 5 Irene Ball 6
holmes middle school R 6 Wynona James 6
holmes middle school M 7 Moneaka Elam 8
holmes middle school M 8 Doris Stephens 8
holmes middle school G 9 Catherine Clark 8
holmes middle school M 10 Elma Kemp 8
holmes middle school M 11 Lois Pagett 8
holmes middle school M 12 Ruby Partin 8
holmes middle school H 13 Fances Nugent 8
;
run;
proc sort data=school out=school;
by schoolname ID Qtype;
run;
data school1;
set school end=eof;
by schoolname ID Qtype;
if ID=-1 and Qtype=5 then principal=TeacherName;
if ID=0 and Qtype=7 then Vice=TeacherName;
run;
the expected output.
| schoolname | teachername | subject | ID | Qtype | principal | Vice |
| holmes middle school | Doris Wilson | M | -1 | 5 | Doris Wilson | Amy Smith |
| holmes middle school | Amy Smith | M | 0 | 7 | Doris Wilson | Amy Smith |
| holmes middle school | Mrs. A. T. Whitecotton | R | 1 | 6 | Doris Wilson | Amy Smith |
| holmes middle school | Kemp Elma | M | 1 | 8 | Doris Wilson | Amy Smith |
| holmes middle school | Pagett Lois | M | 1 | 8 | Doris Wilson | Amy Smith |
| holmes middle school | Partin Ruby | M | 1 | 8 | Doris Wilson | Amy Smith |
| holmes middle school | Nugent Fances | H | 1 | 8 | Doris Wilson | Amy Smith |
| holmes middle school | Geraldine Carpenter | M | 2 | 6 | Doris Wilson | Amy Smith |
| holmes middle school | Crigler Jennie | R | 3 | 6 | Doris Wilson | Amy Smith |
| holmes middle school | Lida Gray Gibbs | M | 4 | 6 | Doris Wilson | Amy Smith |
| holmes middle school | Ball Irene | M | 5 | 6 | Doris Wilson | Amy Smith |
| holmes middle school | James Wynona | R | 6 | 6 | Doris Wilson | Amy Smith |
| holmes middle school | Elam Moneaka | M | 7 | 8 | Doris Wilson | Amy Smith |
| holmes middle school | Stephens Doris | M | 8 | 8 | Doris Wilson | Amy Smith |
| holmes middle school | Clark Catherine | G | 9 | 8 | Doris Wilson | Amy Smith |
| river middle school | Ione Ransdell | M | -1 | 5 | Ione Ransdell | AElgelina Atterbury |
| river middle school | AElgelina Atterbury | M | 0 | 7 | Ione Ransdell | AElgelina Atterbury |
| river middle school | MWynona James | R | 1 | 6 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Hopson Nannie | M | 1 | 8 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Lutie Caldwell | M | 1 | 8 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Maydee Mallory | M | 1 | 8 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Lois Pagett | H | 1 | 8 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Abigail Marlow | M | 2 | 6 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Jennie Crigler | R | 3 | 6 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Lida Gray Gibbs | M | 4 | 6 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Ball Irene | M | 5 | 6 | Ione Ransdell | AElgelina Atterbury |
| river middle school | James Wynona | R | 6 | 6 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Elam Moneaka | M | 7 | 8 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Carpenter Imogene | M | 8 | 8 | Ione Ransdell | AElgelina Atterbury |
| river middle school | Flowers Martha | G | 9 | 8 | Ione Ransdell | AElgelina Atterbury |
