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! 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.