Desktop productivity for business analysts and programmers

How to combine 2 char columns and add a comma separator

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

How to combine 2 char columns and add a comma separator

Hi,

I'm new to using EG and I'm amending a flow built by someone who has since left. It's EG 4.3 (so a bit elderly) and the input dataset structure has changed since the flow was originally built.. I'm trying to combine address data to fit and it seems simple enough but I'm struggling. Any help that I can try would be gratefully received please! 

 

Data example:

Field 1                                                                                           Field 2

14 Anywhere Street, Town, County                                              LR12 6HG

 

I need to add the postcode into the same field as the first part of the address but with a comma after the county and before the postcode. The output should look like:

Field 1

14 Anywhere Street, Town, County, LR12 6HG

 

The output will be feeding into a subsequent code node so has to have the correct input structure or it fails. I don't know how to SAS code to try and amend the code node so I'm stuck! I've found heaps of guidance for removing commas but not for adding one in.

Many thanks Smiley Frustrated


Accepted Solutions
Solution
‎11-27-2017 03:04 PM
Super User
Super User
Posts: 8,274

Re: How to combine 2 char columns and add a comma separator

SAS now has a lot of interesting CAT....() functions.

CATX() is useful in this case.

field1=catx(',',field1,field2);

View solution in original post


All Replies
Super User
Posts: 10,557

Re: How to combine 2 char columns and add a comma separator

Just concatenate, but take care of trailing blanks:

field1 = trim(field1) !! ', ' !! field2;

Also note that field1 needs to be defined with a length sufficent to hold the concatenated values, so you might need to create a new variable, as you can't change the length of a variable on-the-fly.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: How to combine 2 char columns and add a comma separator

Posted in reply to KurtBremser

Ah thank you very much, I haven't used trim before - I'll try this when I'm back in work tomorrow Smiley Happy

Solution
‎11-27-2017 03:04 PM
Super User
Super User
Posts: 8,274

Re: How to combine 2 char columns and add a comma separator

SAS now has a lot of interesting CAT....() functions.

CATX() is useful in this case.

field1=catx(',',field1,field2);
New Contributor
Posts: 3

Re: How to combine 2 char columns and add a comma separator

Thank you all for your quick help, it was much appreciated. I hadn't thought about the length of the field, and when I checked it was auto-set to 200 characters so plenty of room but by luck rather than judgement.

Thanks again 

Super User
Posts: 6,931

Re: How to combine 2 char columns and add a comma separator

Regardless of the approach you take, one of the issues to consider is the lengths of the variables involved.  Make sure FIELD1 is long enough to hold the combined values (otherwise it is possible to lose characters from the end of the combined string).  If you need to adjust the length of FIELD1, do it before the SET statement:

 

data want;

length field1 $ 100;

set have;

*** whatever approach you choose;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 282 views
  • 3 likes
  • 4 in conversation