How to fill in missing values for one variable by group/other var (if all other values are the same)

[Clo13]

Hi all,

I'm trying to complete a mapping of ICD10 codes to conditions by filling in missing values for variable "condition". I've created a variable icd10_grp that will identify groups of codes (by their first three digits). Within these groups I want to fill in the missing condition value with one of the present values (for condition) in the same group but ONLY when all the values in the group are the same. I've tried to use the update statement and that works when the missing value is not the first one (but doesn't discern if all values in group are the same). I've also tried coalesce function and it runs without error but the new column is still blank (and it doesn't discern if all values in group are the same either). How can I adjust the programming to identify all same values of condition within a group and then use that value to replace the missing one?

Thank you.

icd10 mapping to conditions

Product: Base SAS via Enterprise Guide 8.2

Release: 9.04.01 M6P110718

OS: Linux

[Reeza · Accepted solution]

SQL is usually easier.

Something like:

proc sql;
create table want as
select *, 
case when max(condition)=min(condition) then 'Constant' 
else 'Mixed' 
end as cond_status, 
case when not missing(condition) then condition
when missing(condition) and calculated status='Constant' then max(condition) else '' 
end as new_condition
from yourTable
group by icd_grp;
quit;

Untested code as no data

[Clo13]

Hi Reeza,
Thank you! Question, toward the end of the code is that an open/single quotation " before end as new_condition
without a closing quotation "?

[Reeza]

Two single quotes to indicate blank/missing.

[Clo13]

Thank you again. That works but I'm getting one more error with the "group by icd_grp;" statement...and thought perhaps because it's not reading my dataset. How do I point to my dataset in this code? Truly appreciate this - I'm a beginner and need to take more classes. 🙂

[Reeza]

updated my answer.

[Clo13]

@Reeza 

Any chance you can help with this error? So close..

[Reeza]

calculated cond_status not calculated status.

[Clo13]

Hi @Reeza thank you so much for your help. This worked and if you'd like to edit your code sample I'll post it as accepted solution. Thanks for your patience with images. I'm working within 3 layers of remote desktops but I'll make a habit of transferring files in the future (and using this in the next level of my remote desktop). Truly appreciate your help! I've reduced missing values by 33% by resolving the groups that had constant values. You are amazing!

[Clo13]

Making progress! Current error message. TY for help.

[Tom]

---

@Reeza wrote:
Two single quotes to indicate blank/missing.

---

Since SAS stores character variables as fixed length values padded with blanks it is clearer to include the space between the quotes.

then max(condition) else ' ' 

[Clo13]

@Reeza  any thoughts on this error message? Thank you.

[Reeza]

There's not enough information shown to help.
Why are you posting images? Please post the full log and your code/data as text/tables as minimum.

[ballardw]

---

@Clo13 wrote:
Hi Reeza,
Thank you! Question, toward the end of the code is that an open/single quotation " before end as new_condition
without a closing quotation "?

---

Check closely. Looks to be two single quotes with nothing between to assign a missing value to a character variable.

Copy the line of code into any window you can edit and attempt to delete (backspace) the quote. Only one goes away.