topic Re: Merging Issue in SAS Programming

Merging Issue

rush_milo — Wed, 15 Jun 2016 03:28:12 GMT

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!

Re: Merging Issue

RahulG — Wed, 15 Jun 2016 03:47:01 GMT

Use scan statement to extract string before colon.

data Table1_new;

set Table1;

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

run;

Re: Merging Issue

PGStats — Wed, 15 Jun 2016 03:56:19 GMT

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;

Re: Merging Issue

RahulG — Wed, 15 Jun 2016 04:01:55 GMT

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

Thanks for sharing.

Re: Merging Issue

rush_milo — Wed, 15 Jun 2016 04:42:17 GMT

Thank you both RahulG and PG Stats! 🙂