BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vanarsdale2
Fluorite | Level 6

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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. 


 

View solution in original post

7 REPLIES 7
Tom
Super User Tom
Super User

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;
vanarsdale2
Fluorite | Level 6

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?

Tom
Super User Tom
Super User

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

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

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. 


 

vanarsdale2
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1445 views
  • 7 likes
  • 3 in conversation