topic Re: How to concatenate 2 columns and have the output read into an existing column? in SAS Software for Learning Community

How to concatenate 2 columns and have the output read into an existing column?

JibJam221 — Fri, 03 Nov 2023 19:20:52 GMT

Hello everyone,

I have a table that consists of 3 columns: "Name" "FirstName" "LastName".

The "name" column is half filled, the other half is blank.

The "firstname" and "lastname" columns have the other half of the data. I'd like to concatenate these two columns and have the output placed into the "name" column where the values are empty.

proc SQL; 
SELECT
  Last_Name || ' ' || First_Name AS Name
FROM work.table1;

run;

this is what I have for the concatenation of the 2 columns, but im not sure how I can write in to have this placed into the NAME column when the value is empty.

Any advice is appreciated,

thanks!

Re: How to concatenate 2 columns and have the output read into an existing column?

PaigeMiller — Fri, 03 Nov 2023 19:39:15 GMT

Please provide a few examples.

Re: How to concatenate 2 columns and have the output read into an existing column?

ballardw — Mon, 06 Nov 2023 13:49:15 GMT

You might try

proc SQL; 
SELECT
  coalescec( name, catx(' ',Last_Name , First_Name))  AS Name
FROM work.table1;

run;

The COALESCEC function, and its partner Coalesce for numeric variables, returns as the result the first value in the list of values provided. So if Name is blank the result of the catx function is returned. I use CATX because it will strip extra spaces from the parameters an place only one space, if needed, between the last and first names.

@JibJam221 wrote:

Hello everyone,

I have a table that consists of 3 columns: "Name" "FirstName" "LastName".

The "name" column is half filled, the other half is blank.

The "firstname" and "lastname" columns have the other half of the data. I'd like to concatenate these two columns and have the output placed into the "name" column where the values are empty.
proc SQL; 
SELECT
  Last_Name || ' ' || First_Name AS Name
FROM work.table1;

run;
this is what I have for the concatenation of the 2 columns, but im not sure how I can write in to have this placed into the NAME column when the value is empty.

Any advice is appreciated,

thanks!

Re: How to concatenate 2 columns and have the output read into an existing column?

JibJam221 — Mon, 06 Nov 2023 13:46:51 GMT

for sure!

Current Table:

Fitst Name	Last Name	Name
		Marsha Jones
John	Doe
Jane	Levis
		Marianne Phil
		Marsha Jones
Mandy	Smith
		Thomas Cruz

Desired table:

Name

Marsha Jones

John Doe

Jane Levis

Marianne Phil

Marsha Jones

Mandy Smith

Thomas Cruz

So im looking for a code that can concatenate the "first name" and "last name" columns, and insert the values into the pre-existing 'Name' Column, wherever there are blanks.

Re: How to concatenate 2 columns and have the output read into an existing column?

JibJam221 — Mon, 06 Nov 2023 13:47:57 GMT

this code concatenates, however the output is into a whole new column instead of the pre-existing name column. the "name" column has some names in it already, and i'd like the first name & last name values to be inserted into the "name" column where there are blanks

Re: How to concatenate 2 columns and have the output read into an existing column?

PaigeMiller — Mon, 06 Nov 2023 13:49:40 GMT

data want;
    set have;
    if missing(name) then name=catx(' ',first_name,last_name);
run;

Re: How to concatenate 2 columns and have the output read into an existing column?

Tom — Mon, 06 Nov 2023 13:52:31 GMT

Assuming NAME is defined long enough and the pattern of either NAME having a value or the other two having values is consistent then just replace name with combination of all three.

data want;
  set have;
  name=catx(' ',name,firstname,lastname);
run;

If NAME is not long enough then change it s length first.

data want;
  length name $100 ;
  set have;
  name=catx(' ',name,firstname,lastname);
run;