BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EinarRoed
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
EinarRoed
Pyrite | Level 9
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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 815 views
  • 1 like
  • 2 in conversation