topic Re: Generating dynamic (where)based on table in SAS Programming

Generating dynamic (where)based on table

ammarhm — Wed, 19 Dec 2018 00:21:03 GMT

Hi everyone,

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 ID in (24, , 54, 92, 6, 7) ) OR

(group=B and ID in (25, 11, 97)) OR

(group=C and ID in (13, 18)) OR

(group=D and ID in (81, 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: Generating dynamic (where)based on table

Reeza — Wed, 19 Dec 2018 00:38:07 GMT

It's entirely possible though a left join would definitely be faster. Create two sets of macro variables, one that holds the group and one that holds the amounts.
Then loop through them and call the macro as desired.

Re: Generating dynamic (where)based on table

Reeza — Wed, 19 Dec 2018 01:01:29 GMT

Here's an example of how you can implement this. The exact implementation for your data set will be up to you. Note where the semicolons are, they are important in this type of problem.

*create fake data;

data selection;
    input sex $ age;
    cards;
F 12
F 13
F 16
M 12
M 15
M 16
;
    ;
    ;
    ;
run;

*sort - can be assumed;

proc sort data=selection;
    by sex age;
run;

*create macro variables;

data _null_;
    set selection end=eof;
    *list has to be large enough to store the values;
    length list $200.;
    by sex;
    *keep values for last row to create macro variables;
    retain counter list;

    if first.sex then
        do;
            counter+1;
            call missing(list);
        end;
    list=catx(',', list, age);
    *create macro variables at the last record of each sex;

    if last.sex then
        do;
            call symputx(catt('group', counter), sex);
            call symputx(catt('member', counter), list);

            if eof then
                call symputx('Nloops', counter);
            output;
        end;
run;

*Test the macro variable creations;
%put &group1;
%put &group2.;
%put &member1;
%put &member2;
%put &nloops;
*macro to generate the conditions desired;

%macro loop_conditions();
    %do i=1 %to &nloops;
        %*build your criteria here, note the lack of a semicolon at the end;
        (Sex="&&Group&i." %str(AND) Age in (&&member&i)) %*add OR except for last;

        %if &i ^=&nloops %then
            %str(OR);
    %end;
    ;
%mend;

*example usage;

proc sql;
    create table want as 
    select * 
    from sashelp.class 
    where %loop_conditions();
quit;

@ammarhm wrote:

Hi everyone,

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 ID in (24, , 54, 92, 6, 7) ) OR

(group=B and ID in (25, 11, 97)) OR

(group=C and ID in (13, 18)) OR

(group=D and ID in (81, 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

Am

Re: Generating dynamic (where)based on table

r_behata — Wed, 19 Dec 2018 01:16:32 GMT


data a;
input Group $ ID;
cards;
A 24
A 54
A 92
A 6
A 7
B 25
B 11
B 97
C 13
C 18
D 81
D 10
;
run;

Data tmp;
length grp idp idp2 $25 final $100;
idp=' ';

do until(last.group);

	set a;
	by Group;
	
	idp=catx(',',idp,id);
end;

grp=quote(cats(Group)); 
idp2=cats('(',idp,')');

final=catt('(group= ',grp, ' and Id in ',idp2,')');

keep final;
run;


proc sql;
	Select final into :selection separated by ' OR '
	from tmp
	;
quit;

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

Re: Generating dynamic (where)based on table

ShiroAmada — Wed, 19 Dec 2018 01:43:00 GMT

Try this.

data list;
  infile datalines;
  input
  group: $1.
  id: $3.;
datalines;
A 24 
A 54 
A 92 
A 6 
A 7 
B 25 
B 11 
B 97 
C 13 
C 18 
D 81 
D 10 
;
run;


proc sql noprint;
  select catt(count(distinct group)) into :grpn from  list;

  select distinct group into :grp1-:grp&grpn. from list;
quit;

%macro test;
  %do i=1 %to &grpn.;
    
  proc sql noprint;
    select distinct id into: ids separated by "," 
    from list
		  where group="&&grp&i.";
  quit;
  
  %put where group="&&grp&i" and id in (&ids.);
  %end;
%mend;
%test;