topic Re: question on substring in SAS Programming

question on substring

hdg — Fri, 05 Aug 2016 22:10:00 GMT

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

3540

table B

code

35,90

36,3540

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

10,1010 10

Thanks so much!

Re: question on substring

Reeza — Fri, 05 Aug 2016 22:53:17 GMT

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

Re: question on substring

KachiM — Sat, 06 Aug 2016 01:16:14 GMT

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;

Re: question on substring

PGStats — Sat, 06 Aug 2016 02:55:21 GMT

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 .

Re: question on substring

Patrick — Sat, 06 Aug 2016 03:47:37 GMT

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;

Re: question on substring

hdg — Tue, 23 Aug 2016 14:45:42 GMT

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

10,1010 a10

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

Re: question on substring

Reeza — Tue, 23 Aug 2016 17:36:13 GMT

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