BookmarkSubscribeRSS Feed
Emma21
Calcite | Level 5

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. 

 

7 REPLIES 7
Reeza
Super User

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
Calcite | Level 5

Hi Reeza,

it shows.

Emma21_0-1683833978749.png

my icd-phecode library have some repeat code.

Emma21_1-1683834089228.png

Thank you.

Reeza
Super User
How do you want to handle that casem, when you have 20 mapped to both 008 and 0085?
Emma21
Calcite | Level 5

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
Super User
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
Calcite | Level 5

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
Super User
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?

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 251 views
  • 0 likes
  • 2 in conversation