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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.