topic Re: dynamically generate macro variable for SQL Join on LIKE statement in SAS Programming

dynamically generate macro variable for SQL Join on LIKE statement

ammarhm — Wed, 19 Dec 2018 05:57:37 GMT

This is a similar post to my previous one but with modification to use LIKE with a wild card instead (apology to moderators)

a quick question. i have a table (Table A) that contains two columns looking like this

Group	ID
A	24
A	54
A	92
A	6
A	7
B	25
B	11
B	97
C	13
C	18
D	81
D	10

Then I have a very large table (table B) from which i want to extract data based on the group and ID in a proc sql.

For a number of reasons, I wont be able to use a left join or inner join statement, so I am trying to come up with a different approach that would look like this

proc sql,
create table TABLEC as
select *
from tableB
where &selection

the macro variable &selection should be generated dynamically based on tableA and should at the end look like this

(group=A and (compress(ID,,’p’) like '%24%' or compress(ID,,’p’) like '%54%' or compress(ID,,’p’) like '%92%' or compress(ID,,’p’) like '%6%' or compress(ID,,’p’) like '%7%') ) or

(group=B and (compress(ID,,’p’) like '%25%' or compress(ID,,’p’) like '%11%' or compress(ID,,’p’) like '%97%' )) or

(group=C and (compress(ID,,’p’) like '%13%' or compress(ID,,’p’) like '%18%')) or

(group=D and (compress(ID,,’p’) like '%81%' or compress(ID,,’p’) like '%10%' ))

The number and name of groups is variable so the code should account for that too. The only thing that wont change is the name of columns (group, ID).

Has anyone done something similar before?

Yes, I know left join makes more sense but as I said, I need to this differently...

Thanks

Re: dynamically generate macro variable for SQL Join on LIKE statement

Astounding — Wed, 19 Dec 2018 06:11:04 GMT

Certainly this would be simplified if you were to concatenate the two fields. Here is the idea:

proc sql;

create table TABLEC as

select * from TABLEB

where cats(group, id) in (select cats(group, id) from tableA);

quit;

I might have the syntax slightly wrong here, but the idea should work with a little tweaking.

Re: dynamically generate macro variable for SQL Join on LIKE statement

ammarhm — Wed, 19 Dec 2018 07:19:34 GMT

Thanks @Astounding

This approach will however not work if I am trying to use like %id%,

Thanks anyhow for your time

Re: dynamically generate macro variable for SQL Join on LIKE statement

RW9 — Wed, 19 Dec 2018 09:33:46 GMT

You have a really bad setup there then, no getting away from that point. Have to use sql, can't use joins, have to bang this nail into the wall can't use hammers - you see where I am going.

The simplest way to continue this mess is obviously to generate the code from the base table, though don't be suprised if the whole process falls over every run:

data _null_;
  set have end=last;
  if _n_=1 then call execute('proc sql; create table tablec as select * from tableb where ');
  call execute(cats('(group="',group,'" and index(compress(id,,"p")',id,'))'));
  if last then call execute(';quit;');
run;

This will create the proc sql with one group= for each line in the base dataset. Note I use index() rather than like to avoid all the nasty % issues, the effect is the same, as is only doing one id per row rather than all in one row.

Again, I strongly advise to rethink your whole process including how you got to this mess in the first place.

Re: dynamically generate macro variable for SQL Join on LIKE statement

ammarhm — Wed, 19 Dec 2018 10:16:52 GMT

Thanks @RW9

I agree, it is not the best situation to be in, yet again, it also good to be in these situations as you always learn something new. I never thought of using index() so thanks.