DATA Step, Macro, Functions and more

Merging Issue

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Merging Issue

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!


Accepted Solutions
Solution
‎06-15-2016 12:40 AM
Regular Contributor
Posts: 240

Re: Merging Issue

Use scan statement to extract string before colon.

 

data Table1_new;

set Table1;

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

run;

View solution in original post


All Replies
Solution
‎06-15-2016 12:40 AM
Regular Contributor
Posts: 240

Re: Merging Issue

Use scan statement to extract string before colon.

 

data Table1_new;

set Table1;

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

run;

Respected Advisor
Posts: 4,644

Re: Merging Issue

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;
PG
Regular Contributor
Posts: 240

Re: Merging Issue

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

Thanks for sharing.

Occasional Contributor
Posts: 8

Re: Merging Issue

Thank you both RahulG and PG Stats! Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 286 views
  • 5 likes
  • 3 in conversation