topic Re: How do I validate a customer name field in SAS Programming

How do I validate a customer name field

JoeJ — Fri, 08 Dec 2023 15:58:38 GMT

Hi I am looking to validate a customer name field. I need to only include values that contain the following information:

Acceptable customer name example formats:

last name, first name

Doe, Jane

Doe, John

Anthony-Smith, Jim

Requirements:

- customer name is doesn't contain numbers or special characters. There are cases where the name is hyphenated, which is acceptable. I was trying this code but not able to get it to work -

select upcase(strip(compbl(COMPRESS(COMPRESS(cust_name, ""~!@#$%^&*()-_=+\|[]{};:'‘’,.<>?/""),'""""')))) from my_data;

- Need to confirm that the length for the last and first name have a min length of 1 byte each. I am able to determine the length of the field, not sure how to validate the length prior to the comma and preceding the comma.

select LENGTH(CUST_NAME) AS LEN FROM MY_DATA;

thanks in advance

Re: How do I validate a customer name field

Tom — Fri, 08 Dec 2023 16:30:18 GMT

Why COMPRESS? Are you trying to VALIDATE the value or FIX the value?

If you want to check for invalid characters you could use INDEXC() function.

If you want to check that only VALID characters appear then you could use the VERIFY() function.

You should count how many commas. There should be only 1.

Once you know there is only one comma you can use SCAN() to pull out the first and last name parts so you can test their length. length(scan(cust_name,1,',')) or length(scan(cust_name,2,','))

Re: How do I validate a customer name field

JoeJ — Fri, 08 Dec 2023 16:42:37 GMT

The command I posted with compress was just me experimenting. I wasn't sure that was the correct option.

the cust name field has junk data in it like invalid characters and numbers. I am not familiar with the functions you listed, INDEXC() or VERIFY() . I will research those options.

Correct, there should only be 1 comma. I will try both options to see what result I get.

Thank you for the quick response.

Joe

Re: How do I validate a customer name field

ballardw — Fri, 08 Dec 2023 16:45:18 GMT

In my not so humble opinion, I would say this indicates to an incorrect reading of the data when brought into SAS. First and last names should be separate variables. These combined name fields are right up there with 1960 data processing standards.

I have spent way to much time "fixing" name fields that have stuff combined in one variable to do so if I can possibly avoid it.

Note on posting code, really a good idea to post into either a text or code box opened on the forum with the </> or "running man" icons that appear above the message window. The message window here does reformat pasted text which may lead to the appearance of your Compress function call appearing to have unbalanced quotes and parentheses when I copy it to my editor.

Are you sure your data does not contain any names like "Van Dyke" or "Le Blanc" where there are spaces in the last name? Will that have any impact on your processing.

Re: How do I validate a customer name field

JoeJ — Fri, 08 Dec 2023 17:04:58 GMT

I agree with you 100% that the the name should be broken up into 2 separate fields. Unfortunately I don't have control over how the table was built.

Noted on posting code. It's possible that I messed up the syntax when coding so there could be missing values

Yes it is possible for the data to be formatted like "Van **bleep**" or "Le Blanc". Is there a different way to check for that?

Re: How do I validate a customer name field

ballardw — Fri, 08 Dec 2023 17:38:14 GMT

@JoeJ wrote:

Yes it is possible for the data to be formatted like "Van **bleep**" or "Le Blanc". Is there a different way to check for that?

That's why I copied your function call to my editor. One of the most likely characters to get "reformatted" is a space character. If you don't compress them away or Strip in the wrong order then the Scan that @Tom suggests should have no problem as it only used the comma to delimit values instead of the default behavior that would include spaces.

Unless there is a very good reason not to, i.e. the boss says not to, I would likely use this step as the time to create separate first and last name variables using scan (after defining lengths long enough to hold the longest every expected name value for the variables).

Re: How do I validate a customer name field

JoeJ — Fri, 08 Dec 2023 17:51:42 GMT

If you don't mind could you show me an example of what you are referring too? I am not clear as to what you are saying.

Re: How do I validate a customer name field

Tom — Fri, 08 Dec 2023 18:19:23 GMT

Try something like this:

data have;
  input cust_name $30.;
cards;
Doe,Jane
Doe, John
Anthony-Smith, Jim
James,Hen3ry
Mary Lou Jones
Jim,Bob,Tucker
;

data want;
  set have;
  length fname lname $30 ;
  valid=1;
  if count(cust_name,',') ne 1 then do;
    put 'WARNING: Wrong number of commas. ' cust_name=;
    valid=0;
  end;
  if compress(cust_name,'-,','a') ne ' ' then do;
    put 'WARNING: Invalid character. ' cust_name=;
    valid=0;
  end;
  lname=scan(cust_name,1,',');
  fname=scan(cust_name,2,',');
  if valid then cust_name=catx(', ',lname,fname);
run;

Re: How do I validate a customer name field

JoeJ — Fri, 08 Dec 2023 18:23:48 GMT

Thank you I will try that.

Re: How do I validate a customer name field

Patrick — Sun, 10 Dec 2023 03:55:50 GMT

On top of what others already wrote: If you really want to clean-up your data then ideally use SAS' data quality functions (if licensed). Here an article for Viya but very similar (or even the same) code should already work in SAS9.

Re: How do I validate a customer name field

JoeJ — Tue, 12 Dec 2023 13:33:59 GMT

@Tom I have been working with your example. It's working well. Just have a few questions:

- Where does the output from the put statement get written?

- I didn't specify this in the beginning but I am running this against 38mm records. Is this the most efficient way to process this data?

- In this statement if compress(cust_name,'-,','a') ne ' ' will this identify records that have values other than alpha characters like numeric values?

Joe

Re: How do I validate a customer name field

JoeJ — Tue, 12 Dec 2023 13:35:16 GMT

@Patrick thank you for sharing. Unfortunately, we don't have Viya. wish I did!

Re: How do I validate a customer name field

Tom — Tue, 12 Dec 2023 14:41:27 GMT

PUT statements write to the current output file. If no FILE statement has been run then that is the SAS log

If you expect to have more than a handful of bad records then don't write the warnings to the log. Perhaps you could change the code to put the note into a character variable that you could review later. For example by running a reporting step that list (or perhaps just summarizes) the types of bad values found.

Re: How do I validate a customer name field

JoeJ — Tue, 12 Dec 2023 23:45:34 GMT

@Tom Why would these records be flagged based on this code

if count(cust_nm,',') ne 1 then do;
    warning= 'WARNING: Wrong number of commas. ';
    valid=0;
  end;

CUST_NM	fname	lname	warning
MARY FLOWERS		MARY FLOWERS	WARNING: Wrong number of commas.
TRAVEL FIRST LLC		TRAVEL FIRST LLC	WARNING: Wrong number of commas.

Re: How do I validate a customer name field

SASKiwi — Tue, 12 Dec 2023 23:53:04 GMT

@JoeJ - SAS Data Quality is also available in SAS 9.4, but if you don't have it there would be an extra cost in licensing it.

Re: How do I validate a customer name field

Tom — Wed, 13 Dec 2023 00:02:42 GMT

Huh?

They don't have a comma between the first and last name.