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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.