BookmarkSubscribeRSS Feed
Ashok16
Calcite | Level 5

I have a dataset like below:

 

IDPRIMDX_LINEICD10_TDL1icd10_TDL2icd10_TDL3icd10_TDL4icd10_TDL5icd10_TDL6
1111114821Z23     
1111114821Z23G20M54.16   
1111113021K22.70     
1111113021K22.70Z86.010    
1111113021K63.5K57.30    
1111117511K21.9     
1111117511R13.10R94.5K21.9Z12.11M85.80R03.0
1111117511Z12.11     

 

And my desired output should look like:

 

IDPRIMDX_LINEICD10_TDL1icd10_TDL2icd10_TDL3icd10_TDL4icd10_TDL5icd10_TDL6
1111114821Z23G20M54.16   
11111130  K22.70Z86.010K63.5K57.30  
11111175  R13.10R94.5K21.9Z12.11M85.80R03.0

 

I am new to SAS, please help.

2 REPLIES 2
Reeza
Super User

The method to solve this efficiently is to transpose it to a long format, remove duplicates and then transpose back to a wide format.

Does the order of the ICD10 code matter? This solution assumes it doesn't, and given your data it shouldn't otherwise your second example would have issues.

 

Here's a worked example. Ignore the first step-it just generates sample data to test on, you can replace the dataset name with yours as well as the appropriate variable names.

 

*Generate sample data;
data have;
array dx(6) $ dx1-dx6;
array diag(6) $ _temporary_ ("A", "B", "C", "D", "E", "F");
do id=1 to 6;
id2=id*2;
do j=1 to 6;
if rand('bernoulli', 0.2)=1 then do;
dx(j)=diag(floor(rand('uniform')*6+1));
end;
end;
output;
end;

drop j;
run;

*Flip to long form;
proc transpose data=have out=flipped prefix=diag;
by id id2;
var dx1-dx6;
run;

*remove duplicates;
proc sort data=flipped nodupkey out=flipped_unique;
where not missing(diag1);
by id id2 diag1;
run;

*Flip to wide form;
proc transpose data=flipped_unique out=want prefix=dx;
by id id2;
var diag1;
run;

 

 

ballardw
Super User

Why are PRIM and DX_Line kept for the first line but not for any of the others?

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1102 views
  • 0 likes
  • 3 in conversation