How to select all but some of matching or ID variables in condition statement in SQL?

[Macro]

Hi,

outfile and outfile1 have many variables with Scenario_ID the first variable respectively.  So it is impractical to list all of them. I would like to select all the variables from these two files but without the duplicate Scenario_ID column.

How to do this without a warning message? The following code will cause a warning message of

WARNING: Variable Scenario_ID already exists on file outfile:

proc sql;

  create table outfile2 as

  select a.*, b.*

  from outfile1 as a right join outfile as b

  on a.Scenario_ID=b.Scenario_ID;

quit;

How to let Scenario_ID be the first variable in outfile2 without duplicate Scenario_ID warning sign?

[ballardw]

Explicitly list the variables from b and leave out b.Scenario_ID to remove the warning.

Or live with the warning, it is only reminding you that both data sets contributed. Since they have the same value due to your join there isn't a problem. The warning is for other variables that you don't address, for example a.name and b.name, you may not get the one you want in all cases.

[Tom · Accepted solution]

Change the name of one of them.

proc sql;

  create table outfile2(drop=xxx) as

    select a.*, b.*

    from outfile1 as a

         left join

         outfile(rename=(Scenerio_Id=xxx)) as b

    on a.Scenario_ID=b.xxx

  ;

quit;

[Macro]

Thanks Tom. That works.

[Macro]

Tom, what if there is no create table statement in the above code and only select statement exists. In that case, how to efficiently drop some of variables in the condition statement in output result assuming there are many variables in a and b?