DATA Step, Macro, Functions and more

Build a merge statement from macro values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

Build a merge statement from macro values

[ Edited ]

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

 

 

 

 

 


Accepted Solutions
Solution
‎04-24-2017 10:17 AM
Super User
Super User
Posts: 6,500

Re: Build a merge statement from macro values

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 6,938

Re: Build a merge statement from macro values

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 128

Re: Build a merge statement from macro values

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.

Solution
‎04-24-2017 10:17 AM
Super User
Super User
Posts: 6,500

Re: Build a merge statement from macro values

[ Edited ]

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;
Frequent Contributor
Posts: 128

Re: Build a merge statement from macro values

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!

Super User
Super User
Posts: 7,401

Re: Build a merge statement from macro values

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.

PROC Star
Posts: 7,363

Re: Build a merge statement from macro values

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

 

Super User
Super User
Posts: 6,500

Re: Build a merge statement from macro values

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;
Frequent Contributor
Posts: 128

Re: Build a merge statement from macro values

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.

Super User
Super User
Posts: 6,500

Re: Build a merge statement from macro values

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))
;
Super Contributor
Posts: 474

Re: Build a merge statement from macro values

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

Super User
Super User
Posts: 6,500

Re: Build a merge statement from macro values

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.

Super Contributor
Posts: 474

Re: Build a merge statement from macro values

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 235 views
  • 1 like
  • 6 in conversation