topic How to concatenate strings in separate columns ignoring the blank values in New SAS User

How to concatenate strings in separate columns ignoring the blank values

SASnovice23 — Fri, 24 Mar 2023 05:40:42 GMT

I have a column called "Name" which stores the 8 states and territories in Australia, all upper-cased and spacing replaced with an underscore (i.e. NEW_SOUTH_WALES, VICTORIA, QUEENSLAND, SOUTH_AUSTRALIA, WESTERN_AUSTRALIA, TASMANIA, NORTHERN_TERRITORY, AUSTRALIAN_CAPITAL_TERRITORY). My aim is to create a separate column where the state names are proper case with spacing, so I created 3 individual columns that split the names.

Word1=propcase(scan(Name,1,'_'));
Word2=propcase(scan(Name,2,'_'));
Word3=propcase(scan(Name,3,'_'));

After this step, I used

Word=cat(Word1,Word2,Word3);

to create the column with space in between. However, it only gives me a blank column instead. How can I resolve this?

Re: How to concatenate strings in separate columns ignoring the blank values

Kurt_Bremser — Fri, 24 Mar 2023 06:16:36 GMT

Use the CATX function:

Word = catx(" ",Word1,Word2,Word3);

The extended CAT functions remove leading and trailing blanks on their own.

Re: How to concatenate strings in separate columns ignoring the blank values

andreas_lds — Mon, 27 Mar 2023 06:08:04 GMT

You could use:

Word = propcase(translate(Name, ' ', '_'));

Don't forget to use the length-statement so that the length of "Word" is sufficient.