topic Re: create new array based on old array and another library., like excel Vlookup function in SAS Programming

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

Emma21 — Thu, 11 May 2023 17:50:05 GMT

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.

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

Reeza — Thu, 11 May 2023 19:14:19 GMT

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;

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

Emma21 — Thu, 11 May 2023 19:41:54 GMT

Hi Reeza,

it shows.

my icd-phecode library have some repeat code.

Thank you.

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

Reeza — Thu, 11 May 2023 19:53:27 GMT

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

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

Emma21 — Thu, 11 May 2023 20:00:22 GMT

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.

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

Reeza — Thu, 11 May 2023 20:27:50 GMT

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?

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

Emma21 — Thu, 11 May 2023 22:29:43 GMT

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.

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

Reeza — Thu, 11 May 2023 22:48:55 GMT

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?