BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ammarhm
Lapis Lazuli | Level 10

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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.

View solution in original post

4 REPLIES 4
Reeza
Super User
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.
Reeza
Super User

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

 



 

r_behata
Barite | Level 11

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;





ShiroAmada
Lapis Lazuli | Level 10

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 1352 views
  • 2 likes
  • 4 in conversation