create new array based on old array and another library., like excel Vlookup function

[Emma21]

Hi, 

there are 25 variables all with icd code in a sas files a, I need to create another 25 variables come with phecode based on ICD code and phecode library b.

file a looks like:

Id dx1 dx2....dx25;

file b just have two variables:

icd phecode

I want a dataset like:

id dx1 dx2 ....dx25 phecode1 phecode2 ....phecode25;

I tried below code:

%macro create_phecode_vars;
%do i = 1 %to 25;
proc sql;
create table dxn as
select a.*, b.phecoden as phecode&i
from dxn a left join phecn b
on a.dx&i eq b.icdn
;
quit;
%end;
%mend;

Log show WARNING: This CREATE TABLE statement recursively references the target table. A consequence of
this is a possible data integrity problem.

Any suggestions are appreciated. 

[Reeza]

Use a format instead.
https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/001-30.pdf

data icd_fmt;
set phecn;
fmtname='dxfmt';
type='C';
start=icd;
label = phecode;
run;

proc format cntlin=icd_fmt;
run;

data want;
set have;

array _dx(*) dx1-dx25;
array _phe(*) $10. phecode1-phecode25;

do i=1 to dim(_dx);
if not missing(_dx(i)) then phecode(i) = put(_dx(i), dxfmt.);
end;

run;

[Emma21]

Hi Reeza,

it shows.

my icd-phecode library have some repeat code.

Thank you.

[Reeza]

How do you want to handle that casem, when you have 20 mapped to both 008 and 0085?

[Emma21]

Yes, I have much more ICD code than Phecode, it is not 1-to -1. I just want to do if dx(i) match icd, then phe(i)=phecode. but my database is big and with repeat. 

[Reeza]

Your PHEcodes can repeat without issue, the ICD codes should be unique. In the case above, when you have ICD 20, which one should it map to, 008 or 0085? A join would join it to both and duplicate records or you'd have to filter it someway as well. How would you handle it in that case?

[Emma21]

Would it possible to keep all the matched phecode? The file has 155,881 match lines with just 97,852 unique icd code. Thank you.

[Reeza]

May instead of keeping the DX1-DX25, you should transpose that data and then join it to the ICD code and you can have all the duplicates in a long dataset format instead?