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.
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;
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;
Thank you. Following you suggestion, I solved the issue by:
data want; set have;
if not missing(ret); run;
All non-numeric data will have resulted in missing values upon import, so removing observations with missing values should suffice.
@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.
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 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: