topic Re: PROC SQL with overlap columns in SAS Programming

PROC SQL with overlap columns

hhchenfx — Wed, 17 Feb 2021 04:21:43 GMT

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;

Re: PROC SQL with overlap columns

Shmuel — Wed, 17 Feb 2021 05:08:00 GMT

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;

Re: PROC SQL with overlap columns

hhchenfx — Wed, 17 Feb 2021 15:54:26 GMT

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.

Re: PROC SQL with overlap columns

SASKiwi — Wed, 17 Feb 2021 19:11:40 GMT

Please post your expected output.

Re: PROC SQL with overlap columns

Shmuel — Wed, 17 Feb 2021 19:31:46 GMT

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).

Re: PROC SQL with overlap columns

PhilC — Wed, 17 Feb 2021 20:55:12 GMT

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;