Remove numeric variable with a character value

[Zerg]

I have a dataset downloaded from CRSP and looks like:

id    date    returns

1     xx          B

1     xx          C

1     xx          0.2

2     xx          .

2     xx          0.2

2     xx          C

3     xx          0.5

3     xx          .

3     xx          C

Returns is a numeric variable but somehow contains letters. Now I want to remove observations with letters or missing value in the returns. I can remove missing value observations with:

data want; set have;
if not(ret=.); run;

But if I try to remove observations with letters:

data t2; set t;
if not(ret='B'); run; 

it prompts: Invalid numeric data, 'B'

How can I resolve this? Thank you.

[Tom · Accepted solution]

Numeric variables cannot contain letters.  They can have special missing values which will be printed as the corresponding letter. SAS has 27 special missing values which in code you represent by a period followed by either a letter or underscore character.  So ._,.A,....,.Z.

If you want a specific value then reference that value.

proc print data=have;
  where returns = .C ;
run;

Of if you want to include/exclude ALL missing values then use the MISSING() function.

proc print data=have;
  where not missing(returns);
run;

[Zerg]

Thank you. Following you suggestion, I solved the issue by:

data want; set have;
if not missing(ret); run;

[Kurt_Bremser]

All non-numeric data will have resulted in missing values upon import, so removing observations with missing values should suffice.

[ballardw]

---

@Zerg wrote:

I have a dataset downloaded from CRSP and looks like:

id    date    returns

1     xx          B

1     xx          C

1     xx          0.2

2     xx          .

2     xx          0.2

2     xx          C

3     xx          0.5

3     xx          .

3     xx          C

Returns is a numeric variable but somehow contains letters. Now I want to remove observations with letters or missing value in the returns. I can remove missing value observations with:

data want; set have;
if not(ret=.); run;

But if I try to remove observations with letters:

data t2; set t;
if not(ret='B'); run; 

it prompts: Invalid numeric data, 'B'

How can I resolve this? Thank you.

---

I would suggest searching the source to see if they have a description or possibly a format for what the special missing values represent. The actual meaning may have some impact on your specific use of the records.