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

Hello,

I have a problem, i will join two tables with a function like/*. I explain my problem, i have a table, it's my referencial and the other my table with text.

Table Referencial :

two columns : duree / correspondance

12 years / 144

4 years / 48

2 years / 24

8 years / 96

Table Duration :

two column

id / text

25158 / cat 12 years tom

5896 / spot and 12 years

4565 / 8 years anna

New column (my wish) :

id / text / correspondance

25158 / cat 12 years tom / 144

5896 / spot and 12 years / 144

4565 / 8 years anna / 96

I tried this (http://support.sas.com/resources/papers/proceedings12/122-2012.pdf) but it doesn't really work.

PROC SQL ;

CREATE TABLE

Possible_Matches

AS SELECT

*

FROM Name AS n, Birthday

AS

b

WHERE

(

n

.Last =* b.Last_B OR

n

.First =* b.First_B);

QUIT

;

With this code, if, my column text contains a 2, i will have in correspondance all number with a two, i will have : 2 lines, one with 144 (12 years) and 24 (2 years).

(My wish) SAS must find an equal match. If in my text we find 12 years, so we must have 144 not 24, and if we have 1 in the text, 0 match, if we have 122, 0 match, if we have 2 years = 24.

Thank you !

1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

Hello,

Assuming I get it right , here is a solution:

data ref;
input duree $ & coresp;
datalines;
12 years  144
4 years  48
2 years  24
8 years  96
;
run;


data Duration;
infile datalines truncover;
input id text $25.;
datalines;
25158 cat 12 years tom
5896 spot and 12 years
4565 8 years anna
;
run;


data duration;
length keyvar $ 20 numval $ 5 ;
set duration;
stringtily=substr(text,1,find(text, "years")-1);/*determine from text string until years*/
numval=scan(stringtily,countw(stringtily));/*get number from string determined above - my assumption is that immediately before word years are the numbers*/
keyvar=strip(numval) || " years";/*determine key var*/
run;

proc sql;
select duration.id, duration.text, ref.coresp
from ref inner join duration on duration.keyvar=ref.duree;
quit;

View solution in original post

2 REPLIES 2
Loko
Barite | Level 11

Hello,

Assuming I get it right , here is a solution:

data ref;
input duree $ & coresp;
datalines;
12 years  144
4 years  48
2 years  24
8 years  96
;
run;


data Duration;
infile datalines truncover;
input id text $25.;
datalines;
25158 cat 12 years tom
5896 spot and 12 years
4565 8 years anna
;
run;


data duration;
length keyvar $ 20 numval $ 5 ;
set duration;
stringtily=substr(text,1,find(text, "years")-1);/*determine from text string until years*/
numval=scan(stringtily,countw(stringtily));/*get number from string determined above - my assumption is that immediately before word years are the numbers*/
keyvar=strip(numval) || " years";/*determine key var*/
run;

proc sql;
select duration.id, duration.text, ref.coresp
from ref inner join duration on duration.keyvar=ref.duree;
quit;

olivier_guitton
Calcite | Level 5

Thanks ! Perfect ! It's work !!!!!

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
  • 2 replies
  • 421 views
  • 0 likes
  • 2 in conversation