topic Re: Deleting observations based on the first letter of the observation. in SAS Programming

Deleting observations based on the first letter of the observation.

valarievil — Sat, 12 Oct 2019 23:22:48 GMT

Hello!

I have a dataset with 6 variables. one of those variables is state (california, idaho, nevada, ect.) How would I go about excluding states that don't begin with the letter M?

Re: Deleting observations based on the first letter of the observation.

mkeintz — Sat, 12 Oct 2019 23:32:15 GMT

Editted note: this particular example assume STATE is all capital letters. If not, then use

UPCASE(STATE)

in every place I have used

STATE

If the old days you could

data want;
  set have;
  where state<'M' or state >='N';
run;

But a while ago SAS introduced the "=:" relation, which allows you to:

data want;
  set have;
  where not (state=:'M');
run;

The '"=:" relation, exclusively used for character values, tells SAS to take the two values in question, and see if the shorter one exactly matches the leading characters of the longer one. By "shorter" and "longer", I don't mean storage length. Instead I mean the number of characters in the value excluding the trailing blanks.

Re: Deleting observations based on the first letter of the observation.

valarievil — Sun, 13 Oct 2019 00:21:31 GMT

So I have tried this, but it still produces a data set with all states not just the states that begin with the letter M. I think I need a piece of code like this:

data want;

set have;

if state= "M--------" then keep;

else delete;

run;

But i'm not sure how to go about doing that...

Re: Deleting observations based on the first letter of the observation.

SASKiwi — Sun, 13 Oct 2019 00:27:26 GMT

Alternatively: ne is mnemonic for not equal, and eq for equal

where state ne: 'M';

Re: Deleting observations based on the first letter of the observation.

valarievil — Sun, 13 Oct 2019 00:34:36 GMT

data want;
set have;
if state=:'M' then output;
if state=:'N' then output;
else delete;
run;

My goal was to delete all states that did not begin with the letter M or N. This worked for me. thanks all!

Re: Deleting observations based on the first letter of the observation.

SASKiwi — Sun, 13 Oct 2019 00:42:43 GMT

With the IF statement this is one solution. You should read the documentation on the different types of expressions you can use in an IF statement. Trying to guess how statements work is only going to get you frustrated.

This link is a good one to explore: https://documentation.sas.com/?docsetId=lrcon&docsetTarget=lrconwhatsnew94.htm&docsetVersion=9.4&loca

if state =: "M" then output;

Re: Deleting observations based on the first letter of the observation.

hashman — Sun, 13 Oct 2019 01:34:54 GMT

@mkeintz:

Mark, instead of upcasing the whole thing, can do:

where state in: ("m","M") ;

which in my WHERE tests against sashelp.zipcode(keep=statecode) times 1000 works more than twice as fast compared to:

where upcase (state) =: "M" ;

Looks like UPCASE (and also $UPCASE1. - have tested that, too) is much more expensive than testing for another value via IN.

Kind regards

Paul D.

Re: Deleting observations based on the first letter of the observation.

mkeintz — Sun, 13 Oct 2019 02:58:40 GMT

Nice.