hi,   Can someone explain if/what is the difference in perl,using function  prxmatch, between space and word boundary \b?   Example adding a space (after POBOX): if prxmatch ("m/POBOX /oi",BILL_ADDR_LINE1)> 0    Example adding word boundary \b : if prxmatch ("m/POBOX\b/oi",BILL_ADDR_LINE1)> 0   Thank you  ... View more

  hi, When joining two tables, I need to see all records from table A, that match only once to records from table B. In othe words, if table B has 3 obvervations that matches 1 obs from table A, I only want to see 1 'joined' observations (or if you know of another way to arrive at this result).   thank you in advance. Sample code below   Data table1; Input ID $ Account_ID $ Amount; Datalines; 001 2001 13 002 2002 5 003 2002 4 004 2003 1 Run; Data table2; Input ID $ Account_ID $ Amount; Datalines; 001 2001 13 002 2001 13 003 2001 13 004 2001 13 005 2003 1 Run; Proc sql; create table GET as Select a.*, b.ID AS ID2, B.ACCOUNT_ID AS ACCOUNT_ID2,B. AMOUNT AS AMOUNT2 FROM TABLE1 AS A LEFT JOIN TABLE2 AS B ON A.ACCOUNT_ID=B.ACCOUNT_ID AND A.AMOUNT=B.AMOUNT;QUIT; Data WANT; Input ID $ Account_ID $ Amount ID2 $ ACCOUNT_ID2 $ AMOUNT2 ; Datalines; 001 2001 13 001 2001 13 002 2002 5 "" "" null 003 2002 4 "" "" null 004 2003 1 005 2003 1 Run; (note that the "" in syntax of table Want is meant to appear as blank value)   ... View more

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; ... View more

hi   I'm joining two tables, where I'd like the content from var in column b to be translated into a text of my choice. Instead of my creating another step, can this be done in a single query?   Example code below.   In the final output, I'd like my variable "relationship" to read "Primary" if "0", or "Secondary" if "2".   Data table1; Input ID $ Name $; Datalines; 001 John 004 Marc 007 Jess 012 Peter Run; Data table2; Input ID $ Relationship $ ; Datalines; 001 0 004 2 007 0 012 2 Run; Proc sql; create table sofar as Select a.*,b.relationship From table1 a,table2 b Where a.id=b.id;quit; Thanks ... View more

hi, I sometime have to sum & count variables, and then show the difference between these two variables. Can this be done in one step instead of two? thanks,   data have; input name $ cli cli_letter; datalines; anne 1 1 barb 1 0 bob 1 1 bob 1 0 bob 1 1 mike 1 1 mike 1 1 run; proc sql; create table have2 as select name, count(cli) as cli,sum(cli_letter) as letter_sent from have group by name;quit; data want;set have2; /*create new var as difference between first two */ letter_not_sent=cli-letter_sent;run; ... View more