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