topic Re: Find and Replace - changing "unknown" to missing in SAS Programming

Find and Replace - changing "unknown" to missing

vanarsdale2 — Fri, 25 Jun 2021 18:11:14 GMT

I have 291 "dichotomous" variables, Yes/No. Instead of ' ' [missing] all the missing values are recorded as "unknown".

I know I can use if/then sub-setting to set "Unknown" to " ", but I do not want to have to manually enter all 291 variables.

I was thinking something like this:

data test;
set trim;
if _all_ = "Unknown" then put " ";
run;

but I can't quite figure out what the code would be.

Re: Find and Replace - changing "unknown" to missing

Tom — Fri, 25 Jun 2021 18:16:42 GMT

So you have 291 character variables or length $7 (or longer)?

Why not use a format?

proc format;
  value $ynunk 'Yes'='Yes' 'No'='No' 'unknown',' '=' ' other='Invalid' ;
run;

Then use that format with your 291 variables.

proc freq data=have;
  tables var1-var291;
  format var1-var291 $ynunk. ;
run;

Re: Find and Replace - changing "unknown" to missing

Reeza — Fri, 25 Jun 2021 18:19:42 GMT

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

You need an array instead.

data test;
set trim;

array _vars2recode(*) <list of variables>;

do i=1 to dim(_vars2recode);
    if _vars2recode(i) = 'Unknown' then _vars2recode(i) = .;
end;

run;

Here is a reference that illustrates how to refer to variables and datasets in a short cut list, so you don't have to list them 1 by 1 for the array list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

@vanarsdale2 wrote:

I have 291 "dichotomous" variables, Yes/No. Instead of ' ' [missing] all the missing values are recorded as "unknown".

I know I can use if/then sub-setting to set "Unknown" to " ", but I do not want to have to manually enter all 291 variables.

I was thinking something like this:

data test;
set trim;
if _all_ = "Unknown" then put " ";
run;

but I can't quite figure out what the code would be.

Re: Find and Replace - changing "unknown" to missing

vanarsdale2 — Fri, 25 Jun 2021 18:27:11 GMT

Doesn't the format option just change how the data is presented and not how it is stored? Would that mess up any future logistic regression analyses or anything else?

Re: Find and Replace - changing "unknown" to missing

vanarsdale2 — Fri, 25 Jun 2021 18:29:16 GMT

So you are saying I would be able to use the _All_ keyword in an array? Because I was trying but couldn't with the subsetting

Re: Find and Replace - changing "unknown" to missing

Tom — Fri, 25 Jun 2021 18:58:03 GMT

Arrays cannot mix numeric and character variables. So you can only use _ALL_ variable list in an array if your data step has only defined one type of variable (all numeric or all character). You could use _CHARACTER_ variable list if you wanted.

data want;
  set have;
  array _c _character_;
  do over _c;
    if upcase(_c)='UNKNOWN' then _c=' ';
  end;
run;

Re: Find and Replace - changing "unknown" to missing

Tom — Fri, 25 Jun 2021 18:59:07 GMT

Most PROCS will use the formatted values when creating groups. Try it with your logistic regression.

Re: Find and Replace - changing "unknown" to missing

Reeza — Fri, 25 Jun 2021 19:48:43 GMT

SQL doesn't aggregate by formats but beyond that most SAS procedures treat the formatted values as a single group.

topic Re: Find and Replace - changing &quot;unknown&quot; to missing in SAS Programming

Find and Replace - changing "unknown" to missing

Re: Find and Replace - changing "unknown" to missing

Re: Find and Replace - changing "unknown" to missing

Re: Find and Replace - changing "unknown" to missing

Re: Find and Replace - changing "unknown" to missing

Re: Find and Replace - changing "unknown" to missing

Re: Find and Replace - changing "unknown" to missing

Re: Find and Replace - changing "unknown" to missing

topic Re: Find and Replace - changing "unknown" to missing in SAS Programming