topic Re: Proc SQL - left join on multiple variables - Warning that variables already exist in New SAS User

Proc SQL - left join on multiple variables - Warning that variables already exist

LEINAARE — Tue, 03 Dec 2019 21:28:43 GMT

Hello,

I am working with two very large datasets and would like to use PROC SQL to merge a variable (AdmDiag10) from a dataset (new.AdmDiag10_merge) onto another dataset to avoid unnecessary sort procedures. The new.AdmDiag10_merge dataset has only five variables, the four need to merge the records and the new variable (AdmDiag10). Since I am more comfortable using data steps, I am including below the program I would use (if I wanted to use a data step) to help communicate what I am trying to achieve with a left join.

/*Data step I would use to achieve desired merge*/
data medical_1217;
	merge medical_1217 (in=a)
		new.admdiag10_merge;
	by orsid recordid log file_type;
	if a = 1;
run;


/*SQL Procedure I have developed to attempt a similar join*/
proc sql;
	create table test as
	select a.*, b.*
	from medical_1217 a
	left join new.admdiag10_merge b
	on a.orsid = b.orsid
	and a.recordid = b.recordid
	and a.log = b.log
	and a.file_type = b.file_type;
quit;

When I run the PROC SQL program, I get warning messages for each variable used to join on. Can someone please explain what is going on here?

Thanks,

Ted

Re: Proc SQL - left join on multiple variables - Warning that variables already exist

ballardw — Tue, 03 Dec 2019 21:37:04 GMT

Your warning is coming from this bit of code:

proc sql;
	create table test as
	select a.*, b.*

The * requests ALL the variables. So since both alias A and B have at least 4 variables in common you have requested duplicates for the variables. The JOIN has nothing to do with the warning. Observe:

1730  proc sql ;
1731     create table work.junk as
1732     select name , name
1733     from sashelp.class
1734     ;
WARNING: Variable Name already exists on file WORK.JUNK.
NOTE: Table WORK.JUNK created, with 19 rows and 1 columns.

It is the mention of the same variable name.

If you do not want the warning then only list the variables from B that you want to add to A, or vice versa. Just mention a variable name once.

Unfortunately data steps don't do well with multiple records with the same BY variables in both sets when using Merge and won't allow multiple records in the base set if using UPDATE. So SQL is often the appropriate tool.

Of course SQL can be extremely verbose due to the syntax requirements.

Re: Proc SQL - left join on multiple variables - Warning that variables already exist

LEINAARE — Tue, 03 Dec 2019 21:41:29 GMT

Hi @ballardw

Thanks for your response. I am not very savvy with proc SQL. Could you please explain what you mean by "If you do not want the warning then only list the variables from B that you want to add to A, or vice versa. Just mention a variable name once."

Re: Proc SQL - left join on multiple variables - Warning that variables already exist

LEINAARE — Tue, 03 Dec 2019 21:43:06 GMT

Does that mean that I do not need to use a SELECT statement for alias A?

Re: Proc SQL - left join on multiple variables - Warning that variables already exist

ballardw — Tue, 03 Dec 2019 22:00:38 GMT

@LEINAARE wrote:
Does that mean that I do not need to use a SELECT statement for alias A?

No.

As a minimum you likely want a select like:

select a.*, b.othervar, b.somethingelse, b.thatvar

which explicitly lists the variables from B that you want that are not already in A

You will find SQL users that never use the * for selection because it indicates that you don't really know what you really want or need.

Re: Proc SQL - left join on multiple variables - Warning that variables already exist

kalyan1234 — Thu, 18 Feb 2021 06:23:00 GMT

select variables in right dataset to avoid notes in log
PROC SQL;
Create table exxb as
Select a.* ,b.exstdtc,extpt,medgroup as medex from xba4 as a left join exxx as b
On a.usubjid = b.usubjid and a.visit=b.visit;
Quit;