Deleting observations based on the first letter of the observation.

[valarievil]

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? 

[mkeintz]

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.

[valarievil]

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

[SASKiwi]

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&loc...

if state =: "M" then output;

[SASKiwi]

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

where state ne: 'M';

[valarievil · Accepted solution]

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!

[hashman]

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

[mkeintz]

Nice.