Concatenate in PROC SQL (CAT) returning null values

[InspectahDex]

I'm trying to concatenate firstname, middlename, lastname, and professionaltitle columns to look like this:

John David Smith, MD

However when I try the below cat function within my PROC SQL statement, I get a column that is completely blank with null values:

cat(PROVIDER_FIRST_NAME, " ", PROVIDER_MIDDLE_NAME, " ", PROVIDER_LAST_NAME, ", ", PROF_DESIGNATION) as provider_name

The CATT, CATS, and CATX functions return values as expected but I'm stumped as to why the CAT function returns nothing.

[Reeza · Accepted solution]

---

@InspectahDex wrote:

The CATT, CATS, and CATX functions return values as expected but I'm stumped as to why the CAT function returns nothing.

---

The other functions trim (remove blank spaces) by default and CAT does not. 

Does this work for you:

cat(trim(PROVIDER_FIRST_NAME), " ", trim(PROVIDER_MIDDLE_NAME), " ", trim(PROVIDER_LAST_NAME), ", ", trim(PROF_DESIGNATION)) as provider_name

---

@InspectahDex wrote:

I'm trying to concatenate firstname, middlename, lastname, and professionaltitle columns to look like this:

John David Smith, MD

However when I try the below cat function within my PROC SQL statement, I get a column that is completely blank with null values:

cat(PROVIDER_FIRST_NAME, " ", PROVIDER_MIDDLE_NAME, " ", PROVIDER_LAST_NAME, ", ", PROF_DESIGNATION) as provider_name

The CATT, CATS, and CATX functions return values as expected but I'm stumped as to why the CAT function returns nothing.

---

[InspectahDex]

It does but another issue is popping up. When the provider has no middle name, there are 3 spaces instead of 1 because of that rule. Is there anyway to account to have a single space for instances where a middle name value is null?

[InspectahDex]

I found a solution to that second issue. I just added compbl before the (cat(trim... and it worked! Thanks, Reeza!

[ChrisNZ]

CAT() does this (assuming the variable lengths here):

'John                                                                 ' || 
'David                                                                ' ||
'Smith                                                                ' ||
', ' ||
'MD                                                                   '       

which does not fit in your result variable.
If PROVIDER_FIRST_NAME is empty, you may well run out of space to see anything at all.

[Tom]

Hard to tell without seeing the context you are using this.

But the CAT() function is inappropriate for what you are doing.

You will not get 

John David Smith, MD

as the result unless PROVIDER_FIRST_NAME is defined as length $4 and PROVIDER_MIDDLE_NAME is defined as length $5 and PROVIDER_LAST_NAME is defined as length $5 and PROF_DESIGNATION is defined as length equal to longer than $2.

That is because the CAT() function, unlike the other functions you mentioned, does NOT trim the trailing spaces from the fixed length character strings that SAS uses.

[InspectahDex]

Ahh, thanks for putting it that way. That makes sense!