topic Re: Adding a space for names in a column in All Things Community

Adding a space for names in a column

jwint12 — Mon, 08 Jul 2024 16:12:17 GMT

Hi everyone,

I have a data set in excel that has Owner City, Owner state, and owner zip in separate columns. I needed to combine these 3 columns into one with a comma separating owner city from owner state and then a space in between owner state and owner zip. Here is the code I have. SAS has correctly combined all columns into 1, which is named combined. Owner city is separated from owner state with a comma, but owner state is not separated from owner zip. This is an example of what is coming back to me. Augusta, GA30909.

I need it to look like Augusta, GA 30909. Thank you to anyone willing to help!!

data aik.props_new1;
set aik.props_news;
combined= cats(Owner_City, ' , ' Owner_State, ' ' , Owner_Zip);
run;

Re: Adding a space for names in a column

PaigeMiller — Mon, 08 Jul 2024 16:36:13 GMT

data a;
    owner_city='Auburn';
    owner_state='GA';
    owner_zip=12345;
    combined=cat(trim(left(Owner_City)), ', ',trim(left(owner_state)), ' ' , trim(left(Owner_Zip)));
run;

You can't use CATS for this because CATS will eliminate text strings that are all blanks, which is what you are seeing.

Re: Adding a space for names in a column

jwint12 — Mon, 08 Jul 2024 16:43:42 GMT

Thank you so much!!

Re: Adding a space for names in a column

Tom — Mon, 08 Jul 2024 16:56:15 GMT

Use CATX() if you want to insert delimiters between strings.

combined= catx(' ',catx(', ',Owner_City, Owner_State), Owner_Zip);

The problem with CATS() is the S which means STRIP(), so it throws away the space. The CATX() function will also strip the values, but not the delimiter sting. So you can create:

New York, NY 10001

from 'New York[,'NY', and '10061'