SQL TRIM, exclude separator character, if source var is blank

[brulard]

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_ADDRESS	TO_ADDRESS
PO BOX 1769      | CORUNNA | ON | N0N1G4 | CAN	956 BLACKTHORN CRES       | CORUNNA | ON | N0N1G7 | CAN
|   |   |   |	|   |   |   |
88 NIVEN ST       | RED DEER | AB | T4P1P8 | CAN	56 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;

[RW9 · Accepted solution]

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.