DATA Step, Macro, Functions and more

Efficiently recode values to missing

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Efficiently recode values to missing

[ Edited ]

Have data like (but over 20 variables):

Input X1-X5;

Cards;

20  10  31  23  42

41  32  44  02  11

22  08  34  82  05

10  45  22  09  10

....; /*over 30,000 rows*/ 

 

Want certain values to be empty so that the output will look like:

 

20          31    23    42

41   32   44            11

22   08           82     26

       21   45     22    09

...

I tried if X1 in ('09', '10', '11') then X1=' '; It works but I have to type 'if then' for all variables. Would there be any short way to solve the problem?

Thanks in advance.

Sijansap


Accepted Solutions
Solution
‎10-01-2017 12:39 PM
Super User
Posts: 13,574

Re: Data Manage

What kind of rules are involved with setting the missing values? You haven't provided much information. Is it only 9, 10 and 11 that get set to missing or something else? Is the rule exactly the same for all 20 variables or are there different rules for the 20 variables?

 

If the same rule is applied to multiple variables then likely an array is part of the answer;

 

data want;

   set have;

   array x x1-x5;

   do i=1 to dim(x);

      if x[i] in ('09','10','11') then call missing(x[i]);

   end;

   drop i;

end;

 

but if the different variables had different rules then you may not have much luck.

View solution in original post


All Replies
Solution
‎10-01-2017 12:39 PM
Super User
Posts: 13,574

Re: Data Manage

What kind of rules are involved with setting the missing values? You haven't provided much information. Is it only 9, 10 and 11 that get set to missing or something else? Is the rule exactly the same for all 20 variables or are there different rules for the 20 variables?

 

If the same rule is applied to multiple variables then likely an array is part of the answer;

 

data want;

   set have;

   array x x1-x5;

   do i=1 to dim(x);

      if x[i] in ('09','10','11') then call missing(x[i]);

   end;

   drop i;

end;

 

but if the different variables had different rules then you may not have much luck.

Contributor
Posts: 24

Re: Data Manage

Hi BallardW,

 

Thank you for your quick reply and suggestion. Your suggestion will help me for X1, and whenever X1 through X5 ( in this case) have same rule to missout. With this at least I have more than half way simplified since some variables have the same rule, and some have different. When there is different rule, I will use your technique and give different names to the variables that have different rule.

 

Thanks again,

Sijansap

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 344 views
  • 0 likes
  • 2 in conversation