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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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