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

brulard — Thu, 22 Jun 2017 12:40:51 GMT

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;

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

RW9 — Thu, 22 Jun 2017 12:53:27 GMT

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.

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
\| \| \| \|	\| \| \| \|
\| \| \| \|	\| \| \| \|

topic SQL TRIM, exclude separator character, if source var is blank in SAS Programming

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

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