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

I was wondering if someone could help me solve this problem maybe using sql (open to other solutions that are fast)

 

I have a table A 

 

code

35

10

20

50

3540

 

table B

 

code

35,90

36,3540

72

10,1010

 

I would like to filter codes in table B by those available in table A, only one code can be selected for each row.

Table b is separated by commas

 

The output I want is the below

 

table B

code          codenew

35,90          35

36,3540      3540

72

10,1010        10

 

 

Thanks so much!

H

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

Assuming the "codes" variables are character below could work as well

proc sql;
  create table want as
    select b.code_s, a.code
    from b left join a
    on find(b.code_s,a.code,'t')>=1
  ;
quit;

View solution in original post

6 REPLIES 6
Reeza
Super User

I think a data step is better in this case.

 

1. Load table A into a temporary array

2. Loop through string searching all values of temporary array. What happens if multiple matches?

 

This is a fairly similar idea, except rather than comparing to values in the temporary array you're checking for equality and then you can create new variable as needed.

 

https://gist.github.com/statgeek/f052b5223fecca066b1f

KachiM
Rhodochrosite | Level 12

Here is Hash approach. You can do this using array too. It is easy to get CODE instead of code_new. If you insist on this name, it is still possible.

 

data a;
input code;
datalines;
35
10
20
50
3540
;
run;

data b;
input code_s $20.;
datalines; 
35,90
36,3540
72
10,1010
;
run;

data want;
   if _n_ = 1 then do;
      if 0 then set a;
      declare hash h(dataset:'a');
      h.definekey('code');
      h.definedone();
   end;
   set b;
   nw = countw(code_s, ',');
   do i = 1 to nw;
      code = input(scan(code_s, i, ','), 8.);
      if h.find() = 0 then output;
   end;
drop i nw;
run;

proc print data = want;
run;
PGStats
Opal | Level 21

A SQL solution could be:

 

proc sql;
create table want as
select b.code_s, a.code
from 
    b left join 
    a 
        on a.code = input(scan(b.code_s,1,","),best.) or
           a.code = input(scan(b.code_s,2,","),best.);
quit;

but join conditions involving the OR operator are notoriously inefficient in SAS/SQL. On the other hand, if you are willing to store codes as in your B table, you can't be very worried about efficiency Smiley Happy.

PG
Patrick
Opal | Level 21

Assuming the "codes" variables are character below could work as well

proc sql;
  create table want as
    select b.code_s, a.code
    from b left join a
    on find(b.code_s,a.code,'t')>=1
  ;
quit;
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

Thank you all for your help. I have a follow up on this question if my table B is

 

table B

 

code

a35,a90

a36,a3540

zz72

a10,x1010

 

 

 

The output I want is the below to only pick those where the codes are available in table A

 

table B

code          codenew

35,90          a35

36,3540      a3540

72

10,1010        a10

 

I tried with find and substring but I am not getting hte answer I want.

Reeza
Super User

Start a new question. Make sure to highlight how it differs and what doesn't work from current solution. 

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
  • 6 replies
  • 1468 views
  • 4 likes
  • 5 in conversation