BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rush_milo
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

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

4 REPLIES 4
RahulG
Barite | Level 11

Use scan statement to extract string before colon.

 

data Table1_new;

set Table1;

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

run;

PGStats
Opal | Level 21

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
RahulG
Barite | Level 11

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

Thanks for sharing.

rush_milo
Obsidian | Level 7

Thank you both RahulG and PG Stats! 🙂

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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