do I need to sanitize a shortcode’s function input?

Of course you do have to sanitize it and escape it.

Otherwise any author/editor of the site will be able to perform any query on your database.

First of all you should never create SQL queries like you do in your code. Never concatenate the raw SQL with anything that is variable or comes from user.

You should use $wpdb->prepare for that.

So your code should look more like that:

$sql = "SELECT COUNT(meta_key) as count FROM {$wpdb->usermeta} WHERE meta_key = %s && meta_value = %s";
    $member_count = $wpdb->get_var( $wpdb->prepare( $sql, 'mepr-address-state', $atts[0] ) );

As you can see, there is also no point in getting all rows (there’s only one) and there’s no point in getting all columns (there also is only one).

That’s the escaping part that will save you from SQL injections.

But yes – most likely you should also put some validation in there – but it depends on what are the available values for that param.