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! 🙂
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.