BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brulard
Pyrite | Level 9

hi,

I produce a report for customer address change (previous and new). There are many address fields in source table that i compress into 1 variable (for previous address). Within this variable, i use "|" as separator. Example below.

 

Sometimes source table address variables are blank for a given customer. Any tip on how i can avoid seeing | | | in my output table?

 

Thanks in advance,

 

output example:

FROM_ADDRESSTO_ADDRESS
PO BOX 1769      | CORUNNA | ON | N0N1G4 | CAN956 BLACKTHORN CRES       | CORUNNA | ON | N0N1G7 | CAN
|   |   |   ||   |   |   |
88 NIVEN ST       | RED DEER | AB | T4P1P8 | CAN56 RUTHERFORD DR       | RED DEER | AB | T4P2Z5 | CAN
|   |   |   ||   |   |   |
|   |   |   ||   |   |   |

 

Example code I used: 

 

PROC SQL;
CREATE TABLE OUTPUT AS
SELECT LEFT(TRIM(bill_addr_line1) || " " || TRIM(bill_addr_line2) || " " ||TRIM(bill_addr_line3) || " | " || TRIM(BILL_CITY) || " | " ||TRIM(BILL_STATE_PROV_CD)|| " | " ||TRIM(BILL_ZIP_POST_CD)|| " | " ||TRIM(BILL_CNTRY_CD)) AS FROM_ADDRESS,
LEFT(TRIM(TO_bill_addr_line1) || " " || TRIM(TO_bill_addr_line2) || " " ||TRIM(TO_bill_addr_line3) || " | " || TRIM(TO_BILL_CITY) || " | " ||TRIM(TO_BILL_STATE_PROV_CD)|| " | " ||TRIM(TO_BILL_ZIP_POST_CD)|| " | " ||TRIM(TO_BILL_CNTRY_CD)) AS TO_ADDRESS
QUIT;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Use the SAS concatenation functions: cats, catt, cat, catx etc.

E.g:

proc sql;
  create table OUTPUT as
  select  catx('|',BILL_ADDR_LINE1,BILL_ADDR_LINE2,BILL_ADDR_LINE3) as BILL_ADDR
  from    HAVE:
quit;

Tip: Avoid using Output as a dataset name, pretty much everything is output from something else.

View solution in original post

1 REPLY 1
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Use the SAS concatenation functions: cats, catt, cat, catx etc.

E.g:

proc sql;
  create table OUTPUT as
  select  catx('|',BILL_ADDR_LINE1,BILL_ADDR_LINE2,BILL_ADDR_LINE3) as BILL_ADDR
  from    HAVE:
quit;

Tip: Avoid using Output as a dataset name, pretty much everything is output from something else.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 545 views
  • 1 like
  • 2 in conversation