Capitalizing first letter of lending companies, while keeping 'LLC' capitalized, and removing...

[BrockJarvie]

Hi, I'm currently working on a problem that asks me to create a new variable from a variable of lender companies, called Lender, into a new variable

called Lender_clean. I must change Lender to having the first letter of all the companies capitalized while keeping 'LLC' capitalized and removing all occurrences of ',LLC', 'NA'  'N/A' and 'N.A' 

PROC SQL;

   CREATE TABLE WORK.QUERY_FOR_FORECLOSURES AS 
SELECT t1.APN,

          t1.Lender, 

      FROM WORK.FORECLOSURES t1;
QUIT;

Below is the current output data of Lender.

I'm trying to as much knowledge as I can over SAS/ProcSql functions as I can, so I'd appreciate any help on how I can do this.

Thanks!

[ballardw]

The basic function is Propcase to make each word with a single capital.

Why do you care about LLC when your second requirement is to remove it?

I would use TRANWRD to remove the LLC, NA, N/A and N.A prior to using Propcase though.

But your requirement is probably incomplete.

For example you have this as a value:

US Bank, N.A., as Trustee, c/o Select Portfolio Servicing

If you remove N.A then you have left over . from the N.A. . So your remove list is incomplete.

Second when you remove N.A. you will have

US Bank, , as Trustee, c/o Select Portfolio Servicing

So likely you need some additional rules regarding commas.

You capitalization rule, assuming we have applied a likely rule for commas, yields this using the PROPCASE function

US Bank, As Trustee, C/O Select Portfolio Servicing

The As looks a bit odd and the C/O another.

Maybe someone will come up with a good regular expression to fix this, I don't use them enough to help there. The approaches I would use a data step as there are likely to be enough conditionals that CASE statements get a bit clunky.

[BrockJarvie]

I was wondering the same thing about LLC. I probably did a bad job of explaining it, but the question is just as unclear to me about it.  Here is the question: 

Clean the Lender variable so that it follows a standard format [e.g., only the first letter is uppercase per word, LLC remains uppercase (, LLC), NA, N/A, and N.A. are removed). Call this new variable Lender_clean.

[ballardw]

---

@BrockJarvie wrote:

I was wondering the same thing about LLC. I probably did a bad job of explaining it, but the question is just as unclear to me about it.  Here is the question: 

Clean the Lender variable so that it follows a standard format [e.g., only the first letter is uppercase per word, LLC remains uppercase (, LLC), NA, N/A, and N.A. are removed). Call this new variable Lender_clean.

---

Given that text I would 1) ask for clarification and 2) without any clarification available assume bad punctuation in the text and just remove the NA, not the LLC.