BookmarkSubscribeRSS Feed
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

Thank you all for your help. I have a follow up on this question that I posted earlier

https://communities.sas.com/t5/Base-SAS-Programming/question-on-substring/m-p/293499#M61090

 

my table A is as before only consistent of the below

code

35

10

20

50

3540

 36x40

 

 

 

table B

 

code

a35,a90

a36,a3540

zz72

jj36x40, jj3640,jj72

a10,x1010

 

I would like the output to be table B where I would like to add an extra column which has the only those codes which are available in table A

 

table B

 

code                     codenew

a35,a90                 a35

a36,a3540             a3540

zz72

jj36x40, jj3640, jj72         jj36x40

a10,x1010              a10

 

 

 

 THanks again for your help

I tried with find but I am not getting the answer I want if there is any other function whcih can help me do this.

Thanks so much

 

3 REPLIES 3
ballardw
Super User

What do you want if there are multiple matches in A?

Since I doubt that your A example has all the values you are concerned with, is there any chance that the A set has a value like 40 which would be a substring of your values 3540 and 36x40? If so would 40 also match anything that 36x40 matches?

In a minor bit, does any character other than a comma separate your values in dataset B?

 

Why doesn't x1010 match 10?

 

You may want to be looking a INDEX as part of the solution but you need to address some of the questions above. 

Ksharp
Super User
First of all, you need to know what kind of character you should clear up .


data code;
input code $;
cards;
35
10
20
50
3540
36x40
;
run;
 
 
 
data B;
input x $80.;
cards;
a35,a90
a36,a3540
zz72
jj36x40, jj3640,jj72
a10,x1010
;
run;
data want;
 if _n_=1 then do;
  if 0 then set code;
  declare hash h(dataset:'code');
  h.definekey('code');
  h.definedone();
 end;
set b;
do i=1 to countw(x,',');
 temp=scan(x,i,',');
 temp1=compress(temp,'x','kd');
 if h.find(key:temp1)=0 then new_code=temp;
end;
drop temp temp1 i code;
run;

Ksharp
Super User
Another one :


data code;
input code $;
cards;
35
10
20
50
3540
36x40
;
run;
 
 
 
data B;
input x $80.;
cards;
a35,a90
a36,a3540
zz72
jj36x40, jj3640,jj72
a10,x1010
;
run;
data want;
 if _n_=1 then do;
  if 0 then set code;
  declare hash h(dataset:'code');
  h.definekey('code');
  h.definedone();
 end;
set b;
do i=1 to countw(x,',');
 temp=scan(x,i,',');
 temp1=prxchange('s/^\D+//',1,temp);
 if h.find(key:temp1)=0 then new_code=temp;
end;
drop temp temp1 i code;
run;


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
  • 3 replies
  • 863 views
  • 1 like
  • 3 in conversation