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! 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.