Hi Everyone,
I want to run 2 proc SQL with list of columns chosen embedded in 2 macro variable &list1 and &list2.
The trouble is that some element in the 2 lists are overlap which make sql return error.
My real work requires to run 2 SQL procedures.
Can you please help me to solve it?
Thank you,
HHCFX
data have1;
input id var1 var2 var3 var4;
datalines;
1 1 1 1 4
2 3 4 3 5
3 4 5 6 9
;run;
data id_file; input id name $;
datalines;
1 name1
2 name2
;run;
%let list1 = var1, var2, var3;
%let list2 = var2, var3, var4;
proc sql;
create table want as select a.*, &list1 from id_file as a join have as b on a.id=b.id; quit;
proc sql;
create table want as select a.*, &list2 from want as a join have as b on a.id=b.id; quit;
LIST1 is used in the first SQL. As VARn (n=1 to 3) are in HAVE1 dataset only, and HAVE1 its assigned in sql as b then the code you can assign:
%let list1 = b.var1, b.var2, b.var3;
LIST2 is used in the second sql. AS variables var2 and var3 exists in both datasets: WANT1 and HAVE1 yo have to decide from which dataset to select those variables - a=WANT1 (a.var2, a.var3)
or b=HAVE1 (b.var2, b,var3).
You did not post the error message you got. I guess the overlapping is next part of your code - the bold part:
create table want as select a.*, &list2
from want as a
join have as b
on a.id=b.id; quit;
Beyond, in both sql you create table want so the 2nd sql override the 1st.
You may rename it as want1, want2 then you will not override results none have overlapping:
data have1;
input id var1 var2 var3 var4;
datalines;
1 1 1 1 4
2 3 4 3 5
3 4 5 6 9
;run;
data id_file; input id name $;
datalines;
1 name1
2 name2
;run;
%let list1 = var1, var2, var3;
%let list2 = var2, var3, var4;
proc sql;
create table want1 as select a.*, &list1
from id_file as a
join have1 as b
on a.id=b.id; quit;
proc sql;
create table want2 as select a.*, &list2
from want1 as a
join have1 as b
on a.id=b.id; quit;
Thanks for looking into the issue.
For the last query, the error is below since var2 and var3 are in both data.
Thanks,
HHC
ERROR: Ambiguous reference, column var2 is in more than one table.
ERROR: Ambiguous reference, column var3 is in more than one table.
LIST1 is used in the first SQL. As VARn (n=1 to 3) are in HAVE1 dataset only, and HAVE1 its assigned in sql as b then the code you can assign:
%let list1 = b.var1, b.var2, b.var3;
LIST2 is used in the second sql. AS variables var2 and var3 exists in both datasets: WANT1 and HAVE1 yo have to decide from which dataset to select those variables - a=WANT1 (a.var2, a.var3)
or b=HAVE1 (b.var2, b,var3).
It is quite frustrating to help you. You must present a want table, somehow, or else we are just guessing what you mean. Do you want a table like this, or something else?
data want;
input id var1 var2 var3 var2_2 var3_2 var4;
datalines;
name1 1 1 1 1 1 4
name2 3 4 3 4 3 5
;run;
Please post your expected output.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.