Merging Issue

[rush_milo]

Hi there,

As shown in the figure below, I would like to merge Table 1 and Table 2 using common key IDs: card_nbr and transaction_name. However, I found out that the transaction names in table 2 are truncated. 

Table 1			Table 2
card_nbr	transaction_name		card_nbr	transaction_name
8000	Harvey Norman 12M: 0/12		8000	Harvey Norman 12M
7000	Ikea 36M: 0/12		7000	Ikea 36M

How do I truncate the transaction name in Table 1 to match the data in Table 2? I have checked the rest of the data and only these installment transactions would have the symbol ":" followed by e.g: 0/12, 3/12. etc. I would like to remove anything from the symbol ":" onwards from Table 1.

Appreciate anyone's kind help to solve this coding issue. 

Many thanks!

[RahulG · Accepted solution]

Use scan statement to extract string before colon.

data Table1_new;

set Table1;

transaction_name_new=scan(transaction_name,1,':');

run;

[PGStats]

Use operator EQT that tests equality up to the length of the shortest string:

data a;
length t1 $8;
do t1 = "aaba", "acdteg", "uret";
    n + 1;
    output;
    end;
run;

data b;
length t2 $8;
do t2 = "aab", "acd", "uretav";
    m + 10;
    output;
    end;
run;

proc sql;
select a.*, b.*
from a, b
where t1 eqt t2;
quit;

[RahulG]

I never knew the option of eqt. This is why Super users makes this community special. 

Thanks for sharing.

[rush_milo]

Thank you both RahulG and PG Stats! 🙂