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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

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
  • 2 replies
  • 844 views
  • 0 likes
  • 3 in conversation