Help using Base SAS procedures

Help needed to create an output in a particular way

Reply
Occasional Learner
Posts: 1

Help needed to create an output in a particular way

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.

Super User
Posts: 17,724

Re: Help needed to create an output in a particular way

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;

 

 

Super User
Posts: 10,454

Re: Help needed to create an output in a particular way

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

Ask a Question
Discussion stats
  • 2 replies
  • 229 views
  • 0 likes
  • 3 in conversation