Dealing with non-numeric and Missing Value

[stacy_not_robot]

I have a column of data(visited_store) which have a mix of numeric, character and missing values.

Example: "Yes", "No", 99,100, .

I would like to create another column, and assign Yes to 1 and all other values to 0.

I tried the following code:

data clean_data;
set original_data;

if visited_store in ( . , 99, 999, 9999) then visited_store_clean = 0;

if visited_store = "No" then visited_store_clean = 0;

if visited_store = "Yes" then visited_store_clean = 1;

RUN;

Does not seem to work.

Any help on how i can select rows with "No", missing value ( . ) and numeric values to 0, while "Yes" to 1?

[PaigeMiller]

Is this column of data in Excel, or a SAS data set, or somewhere else?

A variable in a SAS data set cannot have a mixture of character and numeric variables.

Please explain further what you see and provide more details.

If you are talking about a SAS data set, please convert it to SAS data step code so we can determine what you are seeing and determine how to proceed. Instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

[stacy_not_robot]

Thank you for your response.
This is a column from a sas dataset.

[Reeza]

Run a proc contents on your variable to check the type and format. 

You can do this using proc contents.

proc contents data=original_data;
run;

In general, I would advise you to code Yes as 1, No as 0 and then 99, 9999, 999, . as missing. This way any summary statistics will be counted for correctly. You should also be using IF/ELSE IF not just IF statements. 

data clean_data;
set original_data;

if missing(visited_store) or visited_store in ('99', '999', '9999', '.') then visited_store_clean = .;
else if visited_store = 'No' then visited_store_clean = 0;
else if visited_store = 'Yes' then visited_store_clean = 1;
else visited_store_clean = -99; *this is to check your coding hasn't missed any values;

run;

---

@stacy_not_robot wrote:

I have a column of data(visited_store) which have a mix of numeric, character and missing values.

Example: "Yes", "No", 99,100, .

I would like to create another column, and assign Yes to 1 and all other values to 0.

I tried the following code:

data clean_data;
set original_data;

if visited_store in ( . , 99, 999, 9999) then visited_store_clean = 0;

if visited_store = "No" then visited_store_clean = 0;

if visited_store = "Yes" then visited_store_clean = 1;

RUN;

Does not seem to work.

Any help on how i can select rows with "No", missing value ( . ) and numeric values to 0, while "Yes" to 1?

---

[ballardw]

---

@stacy_not_robot wrote:

I have a column of data(visited_store) which have a mix of numeric, character and missing values.

Example: "Yes", "No", 99,100, .

I would like to create another column, and assign Yes to 1 and all other values to 0.

I tried the following code:

data clean_data;
set original_data;

if visited_store in ( . , 99, 999, 9999) then visited_store_clean = 0;

if visited_store = "No" then visited_store_clean = 0;

if visited_store = "Yes" then visited_store_clean = 1;

RUN;

Does not seem to work.

Any help on how i can select rows with "No", missing value ( . ) and numeric values to 0, while "Yes" to 1?

---

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the <> to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.