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!
Use scan statement to extract string before colon.
data Table1_new;
set Table1;
transaction_name_new=scan(transaction_name,1,':');
run;
Use scan statement to extract string before colon.
data Table1_new;
set Table1;
transaction_name_new=scan(transaction_name,1,':');
run;
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;
I never knew the option of eqt. This is why Super users makes this community special.
Thanks for sharing.
Thank you both RahulG and PG Stats! 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.