topic Re: Concatenate in PROC SQL (CAT) returning null values in SAS Programming

Concatenate in PROC SQL (CAT) returning null values

InspectahDex — Tue, 07 Dec 2021 01:34:22 GMT

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.

Re: Concatenate in PROC SQL (CAT) returning null values

Reeza — Tue, 07 Dec 2021 01:45:18 GMT

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

Re: Concatenate in PROC SQL (CAT) returning null values

ChrisNZ — Tue, 07 Dec 2021 03:49:26 GMT

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.

Re: Concatenate in PROC SQL (CAT) returning null values

Tom — Tue, 07 Dec 2021 03:54:18 GMT

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.

Re: Concatenate in PROC SQL (CAT) returning null values

InspectahDex — Tue, 07 Dec 2021 05:03:06 GMT

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?

Re: Concatenate in PROC SQL (CAT) returning null values

InspectahDex — Tue, 07 Dec 2021 05:09:08 GMT

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

Re: Concatenate in PROC SQL (CAT) returning null values

InspectahDex — Tue, 07 Dec 2021 05:17:25 GMT

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