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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 93
Accepted Solution

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

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;


Accepted Solutions
Solution
‎06-22-2017 05:23 PM
Super User
Super User
Posts: 7,711

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

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


All Replies
Solution
‎06-22-2017 05:23 PM
Super User
Super User
Posts: 7,711

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

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.

☑ This topic is solved.

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

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