topic Re: Remove numeric variable with a character value in SAS Programming

Remove numeric variable with a character value

Zerg — Mon, 20 May 2019 17:12:07 GMT

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.

Re: Remove numeric variable with a character value

Tom — Mon, 20 May 2019 17:23:01 GMT

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;

Re: Remove numeric variable with a character value

Kurt_Bremser — Mon, 20 May 2019 17:19:49 GMT

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

Re: Remove numeric variable with a character value

Zerg — Mon, 20 May 2019 17:24:43 GMT

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

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

Re: Remove numeric variable with a character value

ballardw — Mon, 20 May 2019 17:26:09 GMT

@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.