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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.