Dynamic macro-merge by one or two vars

[Ronein]

Hello,

With continue to my previous question:

I need to perform merge that can by one variable or two variables (depends on number of vars in the macro).

My question:

How to change the following code lines to dynamic code that is taking the variables in List1 macro

on a.Cylinders=b.Cylinders
and a.Horsepower=b.Horsepower

%macro BBB(mon,sourceTbl,TargetTbl,list1=,list2=);
%let nr_vars=%sysfunc(countw(&list1.));
%put &nr_vars.;
proc sql;
create table New_tbl as
select
%do j = 1 %to &nr_vars.;
  put(%scan(&list1.,&j.),%scan(&list2.,&j.).) as %scan(&list1.,&j.),
%end;
  count(*) as nr&mon. format=comma21.,
  calculated nr&mon./(select count(*) as total from  &sourceTbl.) as PCT&mon. format=percent8.1
from &sourceTbl.
%if &nr_vars. > 0
%then %do;
group by 
  %do j = 1 %to &nr_vars.;
  %if &j. > 1 %then ,;
  calculated %scan(&list1.,&j.)
  %end;
%end;
;
quit;


%MACRO RRR1;
%if %sysfunc(exist(&TargetTbl.)) %then %do;
proc sql ;
create table New as
select  a.*,b.nr&mon.,b.PCT&mon.
from &TargetTbl. as a
left join New_tbl as b
on a.Cylinders=b.Cylinders
and a.Horsepower=b.Horsepower
/***Here I want to have a dynamic problem that is taking the variables in List1***/
;
quit;
%end; 

%else %do;
Data &TargetTbl.;
set New_tbl;
Run;
%end; 
%MEND RRR1;
%RRR1;
%mend BBB;
  
/*Distribution by grouped Cylinders+grouped Horsepower***/
/*Distribution by grouped Cylinders+grouped Horsepower***/
/*Distribution by grouped Cylinders+grouped Horsepower***/
proc format;
value F1mt
.='Uknown'
1-4='1-4'
5-high='5+'
;
Run;
proc format;
value F2mt
.='Uknown'
low-80='0-80'
80-120='80-120'
120-180='120-180'
180-high='180+'
;
Run;
%BBB(mon=2204,
sourceTbl=sashelp.cars,
TargetTbl=wanted,
list1=Cylinders Horsepower,
list2=F1mt  F2mt)
  

Data cars2205;/***YYMM**/
input Cylinders Horsepower;
cards;
4 110
4 90
5 120
6 185
4 130
7 250
4 130
5 190
4 76
4 91
8 410
;
run;


%BBB(mon=2205,
sourceTbl=cars2205,
TargetTbl=wanted,
list1=Cylinders Horsepower,
list2=F1mt  F2mt)
 
 

[yabwon · Accepted solution]

Try this:

%macro myJoin(list1, list2);

%local a b c d;

%let a = %scan(&list1.,  1);
%let b = %scan(&list1., -1);

%let c = %scan(&list2.,  1);
%let d = %scan(&list2., -1);

proc sql;
select t1.age, t2.name
from 
sashelp.class as t1
join
sashelp.class as t2
on
t1.&a. = t2.&c.
and
t1.&b. = t2.&d.
;
run;

%mend;


options mprint;
%myJoin(name, name)

%myJoin(name age, name age)

Bart

[Ronein]

May you please explain your code ?
Why did you write minus 1 and plus 1 in scan function?
Will this technique work well when there will be only one variable to match in merge ?

[Ronein]

Great, very clever solution.

As I understand "1"  takes the first element and "-1" takes the last.

If there is one element than first and last are same.

If there are 2 elements then "1" takes the first and  "-1" takes the last.

The only thing need to modify is that need only 2 macro vars a,b because list1 is the explanatory variables and list2 is the correspondence  formats.

Here is the solution that was made thanks to your advice

%macro BBB(mon,sourceTbl,TargetTbl,list1=,list2=);

%local a b nr_vars;
%let a = %scan(&list1.,  1);
%let b = %scan(&list1., -1);
 
%let nr_vars=%sysfunc(countw(&list1.));
%put &nr_vars.;
proc sql;
create table New_tbl as
select
%do j = 1 %to &nr_vars.;
  put(%scan(&list1.,&j.),%scan(&list2.,&j.).) as %scan(&list1.,&j.),
%end;
  count(*) as nr&mon. format=comma21.,
  calculated nr&mon./(select count(*) as total from  &sourceTbl.) as PCT&mon. format=percent8.1
from &sourceTbl.
%if &nr_vars. > 0
%then %do;
group by 
  %do j = 1 %to &nr_vars.;
  %if &j. > 1 %then ,;
  calculated %scan(&list1.,&j.)
  %end;
%end;
;
quit;


%MACRO RRR1;
%if %sysfunc(exist(&TargetTbl.)) %then %do;
proc sql ;
create table New as
select  t1.*,
coalesce(t2.nr&mon.,0 )  as nr&mon. format=comma21.,
coalesce(t2.PCT&mon.,0 ) as PCT&mon. format=percent8.1
from &TargetTbl. as t1
left join New_tbl as t2
on 
t1.&a. = t2.&a.
and
t1.&b. = t2.&b.
/***Here I want to have a dynamic problem that is taking the variables in List1***/
;
quit;
%end; 

%else %do;
Data &TargetTbl.;
set New_tbl;
Run;
%end; 
%MEND RRR1;
%RRR1;
%mend BBB;
  
/*Distribution by grouped Cylinders+grouped Horsepower***/
/*Distribution by grouped Cylinders+grouped Horsepower***/
/*Distribution by grouped Cylinders+grouped Horsepower***/
proc format;
value F1mt
.='Uknown'
1-4='1-4'
5-high='5+'
;
Run;
proc format;
value F2mt
.='Uknown'
low-80='0-80'
80-120='80-120'
120-180='120-180'
180-high='180+'
;
Run;
%BBB(mon=2204,
sourceTbl=sashelp.cars,
TargetTbl=wanted,
list1=Cylinders Horsepower,
list2=F1mt  F2mt)
  

Data cars2205;/***YYMM**/
input Cylinders Horsepower;
cards;
4 110
4 90
5 120
6 185
4 130
7 250
4 130
5 190
4 76
4 91
8 410
;
run;


%BBB(mon=2205,
sourceTbl=cars2205,
TargetTbl=wanted,
list1=Cylinders Horsepower,
list2=F1mt  F2mt)