topic Re: How to fill in missing values for one variable by group/other var (if all other values are the s in SAS Programming

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

Clo13 — Tue, 09 Nov 2021 20:29:43 GMT

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

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Reeza — Tue, 09 Nov 2021 21:19:37 GMT

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

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Clo13 — Tue, 09 Nov 2021 20:50:19 GMT

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 "?

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Reeza — Tue, 09 Nov 2021 20:51:41 GMT

Two single quotes to indicate blank/missing.

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

ballardw — Tue, 09 Nov 2021 20:54:30 GMT

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

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Clo13 — Tue, 09 Nov 2021 21:02:48 GMT

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

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Tom — Tue, 09 Nov 2021 21:12:51 GMT

@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 ' '

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Reeza — Tue, 09 Nov 2021 21:19:54 GMT

updated my answer.

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Clo13 — Tue, 09 Nov 2021 21:20:47 GMT

Making progress! Current error message. TY for help.

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Clo13 — Tue, 09 Nov 2021 21:26:28 GMT

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

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Reeza — Tue, 09 Nov 2021 21:30:48 GMT

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.

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Clo13 — Tue, 09 Nov 2021 21:36:01 GMT

@Reeza

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

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Reeza — Tue, 09 Nov 2021 21:46:24 GMT

calculated cond_status not calculated status.

Re: How to fill in missing values for one variable by group/other var (if all other values are the s

Clo13 — Tue, 09 Nov 2021 23:26:14 GMT

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!