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
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;
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.
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.
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;
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!
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.
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
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;
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.
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))
;
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
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.