Build a merge statement from macro values

[MeganE]

Hi all,

I keep getting SO CLOSE but i can't get the final string to build.  I'm trying to dynamically build a proc sql merge statement with table aliases of A and B.

I have this:

%let merge1=cat_order grp_order year;

And i want to turn it into this:

a.cat_order=b.cat_order and a.grp_order=b.grp_order and a.year=b.year.

The ANDs can be constant, but the merge1 values will be different everytime (sometimes i might have 2 of them, sometimes 3, sometimes 1, etc), so i've been trying to build the statement by parsing the words with countw and scan and i can get it to build part way, but i can't get it to cycle and build completely.

I can't put all of the code here because it's gone through about 10 different versions, so i don't even have some of the stuff i've tested so far, and what's left makes no sense because i got all turned around.  Maybe i was THIS close and just missed one thing, but then i tore it all apart again.  I don't know.

I know in theory it's should be:

count the words. (ie 3)  <-- got this part to work.

create new macro variables with the word "and", one for each count. (join1, join2, join3)  <-- got this part to work.

build a string for each cycle with the "a." aliases, etc onto them ( a.cat_order=b.cat_order and )  <-- got this part to work.

Append the multiple strings together as one string <-- this is where i'm stuck. I built a dataset that has 3 fields with the content i want:

string1=a.cat_order=b.cat_order and

string2=a.grp_order=b.grp_order and

string3=a.year=b.year

But because the # can change, i can't get for the life of me get it to cycle through and retain the first part.  I can't get %let str=str, string&i to work.  I've tried %sysfunc(catx(,str), etc but i keep getting a %sysfuncf error?? 

I don't know.

If anyone can help with the third part, that'd be great.

Thanks

M

[Kurt_Bremser]

data control;
input varname :$20.;
cards;
cat_order
grp_order
year
;
run;

data _null_;
set control end=done;
length wherestring $1000 ws $100;
retain wherestring;
ws = 'a.' !! trim(varname) !! '=b.' !! trim(varname);
wherestring = catx(' and ',trim(wherestring),ws);
if done then call symputx('wherestring',wherestring);
run;

%put &wherestring;

Don't keep a value list in a macro variable; put it in a dataset instead, and use the power of the data step language to process the values.

See Maxim 11.

[MeganE]

The list of variables to merge on comes from a macro variable.  But the first thing i did was put it in to a dataset.

[Tom · Accepted solution]

You do not want to use the CAT...() series of functions with %SYSFUNC().  It is not needed and the fact data SYSFUNC() will need to make a guess as to whether the value is a number or a character string can cause confusion and worse.

If you are writing the code inside of a macro then just use a %DO loop.

%let merge1=cat_order grp_order year;
%let want=;
%let separator=;
%do i=1 %to %sysfunc(countw(&merge1));
   %let word = %scan(&merge1,&i);
   %let want=&want &separator a.&word=b.&word ;
   %let separator=and;
%end;

If not then do the same logic in a data step and use CALL SYMPUTX() to write the result bak into a macro variable.  In a data step you can use the CAT...() functions nicely for this problem.

data _null_;
  merge1="&merge1";
  length word $32 want $1000 ;
  do i=1 to countw(merge1);
    word=scan(merge1,i);
    want=catx(' and ',want,cats('A.',word,'=B.',word));
  end;
  call symputx('want',want);
run;

[MeganE]

I tried the _null_ statement way and it was perfect!  In fact, it got rid of a bunch of other crap i did to try and achieve the same thing.

Thanks!

[RW9]

Start by posting some test data in the form of a datastep, and what the output should look like.  Why are you trying to create a dynamic join, seems a bit peculier to me.  You may find simpler methods to do such a thing.  For example, I have three datasets:

DS=Abc

ID  PARAM   RESULT

DS=Def

ID  PARAM   RESULT

DS=EFG

ID PARAM    OUT

Now I can set these three together, assign a group per filename like below, then use that final dataset to transpose up.  By doing it this way I don't need to know what sets to merge together:

data inter;
  set abc def efg (rename=(out=results)) indsname=nm;
  cat=nm;
run;

proc sort data=inter;
  by id param cat;
run;

proc transpose data=inter out=want;
  by id param;
  var result;
  id cat;
  idlabel cat;
run;

This would give me:

ID PARAM  ABC  DEF  EFG

dataset without mergin all the elements.

[art297]

If I correctly understand what you are trying to do, here is one way you could do it using a SAS macro:

%macro doit(out=,file1=,file2=,criteria=);
  proc sql noprint;
    create table &out. as
      select *
        from &file1. a join &file2. b
          on
            %let i=1;
            %do %while (%scan(&criteria.,&i.,' ') ne );
              %if &i. gt 1 %then and ; 
               a.%scan(&criteria.,&i.,' ')=b.%scan(&criteria.,&i.,' ')
              %let i=&i.+1;
            %end;
    ;
  quit;
%mend doit;

data test1;
  input x y z;
  cards;
3 1 3
2 2 2
1 3 1
;

data test2;
  input x y a;
  cards;
3 1 6
2 2 5
1 3 4
;

%doit(out=want,file1=test1,file2=test2,criteria=x y)

Art, CEO, AnalystFinder.com

[Tom]

Are you trying to build SQL join?

Why not just use a normal data step merge? Then you can use the value as is.

data want ;
  merge table1 table2
  by &merge1;
run;

[MeganE]

Habit.  I haven't merged two datasets through sort, sort, merge in years.  I always do it through proc sql and i already had it, and additional proc sql code to do other stuff written.

[Tom]

You can also take advantage of the NATURAL join.  You can use dataset options if you have some variable name conflicts.

create table want as
  select * 
  from tableA (keep=&merge1 var1 var2)
  natural join
       tableB (keep=&merge1 var1 var3 rename=(var1=b_var1))
;

[DanielSantos]

Found a convenient solution, still for the sake of diversity...

My own approach (which is a modified version from my personal macro functions library).

%macro join(ALIAS1,ALIAS2,VARLIST,VARNUM);
%let varname=%scan(&VARLIST,&VARNUM,%str( ));
%if %str(&varname) ne %str() %then
&ALIAS1..&varname = &ALIAS2..&varname and %join(&ALIAS1,&ALIAS2,&VARLIST,%eval(&VARNUM+1));

%else 1
%mend join;

Used like this:

options mprint;
proc sql noprint;
create table mydata as
select ...
on %join(a,b,cat_order grp_order year,1);
quit;

Will recursively build the join clause appending "and 1" at the end (doesn't affect the AND logic)

MPRINT(JOIN): a.cat_order = b.cat_order and
MPRINT(JOIN): a.grp_order = b.grp_order and
MPRINT(JOIN): a.year = b.year and
MPRINT(JOIN): 1

ALIAS1=left alias,ALIAS2=right alias,VARLIST=columns list,VARNUM=first column to start

Daniel Santos @ www.cgd.pt

[Tom]

Cool method.  

Note that I have found that if using other SQL dialects (like Teradata) you need to use expressions like 1=1 and 1=0 instead of 1 and 0 to represent always true or always false conditions.

[DanielSantos]

Thank you Tom, and you are right. It should be modified as needed.

Actually the original macro has more parameters being one the logical final statement (&LOGIC), which could be:

AND 1

OR 0

or whatever is required to not affect the whole statement logic.

The operator between pairs is obtained from this also (%scan(&LOGIC,1))

Daniel Santos @ www.cgd.pt