topic Treating a warning that doesn't make sense to me (from a basic join) in SAS Programming

Treating a warning that doesn't make sense to me (from a basic join)

EinarRoed — Fri, 22 Oct 2021 13:25:11 GMT

Say I want to create a table called POLICY. It should be identical to the source table POLICY_SRC, except that it should only contain the policies that are represented in the source table POLICY_LOAD. The primary key in all tables is policy_id.

Example code:

proc sql;
	create table POLICY as
	select * from POLICY_SRC src
	inner join POLICY_LOAD load on (src.policy_id = load.policy_id);
quit;

This gives the following warning:

WARNING: Variable policy_id already exists on file WORK.POLICY.

I only select the policy_id column from POLICY_SRC. So why this error?

I guess the code also selects policy_id from POLICY_LOAD, because of the join.

How would you resolve this warning? Can I drop the superfluous column somehow, in this proc sql?

Edit:The reason why I'm doing a SELECT * is that the actual table contain around 150+ columns, so I'd prefer not to list them all if it can be avoided. I'd like to just simply select * columns from POLICY_INPUT.

Re: Treating a warning that doesn't make sense to me (from a basic join)

PaigeMiller — Fri, 22 Oct 2021 13:30:45 GMT

@EinarRoed wrote:

Say I want to create a table called POLICY. It should be identical to the source table POLICY_SRC, except that it should only contain the policies that are represented in the source table POLICY_LOAD. The primary key in all tables is policy_id.

Example code:
proc sql;
	create table POLICY as
	select * from POLICY_SRC src
	inner join POLICY_LOAD load on (src.policy_id = load.policy_id);
quit;
This gives the following warning:

WARNING: Variable policy_id already exists on file WORK.POLICY.

I only select the policy_id column from POLICY_SRC. So why this error?

This is not correct. You have selected all variables from POLICY_SRC. That's what the * indicates. There is a variable named POLICY_ID in both data sets.

In this case, I think it would be fine to ignore the warning, as the information it is providing is expected. (Sometimes warnings provide unexpected information, and then you should not ignore the warning)

Re: Treating a warning that doesn't make sense to me (from a basic join)

EinarRoed — Fri, 22 Oct 2021 13:43:47 GMT

For some reason I thought that the SELECT would only select policy_id from the "FROM-table" and not from the "JOIN-table" unless specified. But yeah I see that it makes sense that it selects all columns from both tables. I'll just live with the warning then, thanks.