I am trying to fill in ID1 variable with same ID number when rdq=adq for each permco in SAS. Here is an example of my data.
permco rdq adq ID ID1
1 333 331 1 .
1 333 332 2 .
1 333 333 3 3
1 333 334 4 .
1 333 335 5 .
1 333 336 6 .
1 555 552 1 .
1 555 553 2 .
1 555 554 3 .
1 555 555 4 4
1 555 556 5 .
1 555 557 6 .
1 555 558 7 .
2 333 331 1 .
2 333 332 2 .
2 333 333 3 3
2 333 334 4 .
2 333 335 5 .
2 333 336 6 .
2 555 552 1 .
2 555 553 2 .
2 555 554 3 .
2 555 555 4 4
2 555 556 5 .
2 555 557 6 .
2 555 558 7 .
And what I desire to have is...
permco rdq adq ID ID1
1 333 331 1 3
1 333 332 2 3
1 333 333 3 3
1 333 334 4 3
1 333 335 5 3
1 333 336 6 3
1 555 552 1 4
1 555 553 2 4
1 555 554 3 4
1 555 555 4 4
1 555 556 5 4
1 555 557 6 4
1 555 558 7 4
2 333 331 1 3
2 333 332 2 3
2 333 333 3 3
2 333 334 4 3
2 333 335 5 3
2 333 336 6 3
2 555 552 1 4
2 555 553 2 4
2 555 554 3 4
2 555 555 4 4
2 555 556 5 4
2 555 557 6 4
2 555 558 7 4
I would like to fill in ID1 with ID number when rdq=adq.
Double DoW loop solution:
data have01;
infile cards truncover expandtabs;
input permco rdq adq ID ID1 ;
cards;
1 333 331 1 .
1 333 332 2 .
1 333 333 3 3
1 333 334 4 .
1 333 333 5 5
1 333 336 6 .
1 555 552 1 .
1 555 553 2 .
1 555 554 3 .
1 555 555 4 4
1 555 556 5 .
1 555 557 6 .
1 555 558 7 .
2 333 331 1 .
2 333 332 2 .
2 333 333 3 3
2 333 334 4 .
2 333 335 5 .
2 333 336 6 .
2 555 552 1 .
2 555 553 2 .
2 555 554 3 .
2 555 555 4 .
2 555 556 5 .
2 555 557 6 .
2 555 558 7 .
;
run;
data want;
do _n_ = 1 by 1 until (last.rdq);
set have01;
by permco rdq;
if first.rdq then call missing(ID1);
if adq = rdq then t_ID1 = ID1;
drop t_ID1;
end;
do _n_ = 1 to _n_;
set have01;
ID1 = t_ID1;
output;
end;
run;
This assumes that if there are multiple matches, the last one should take precedence. If there are no matches then every row for that group gets a missing value.