DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

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

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?


Accepted Solutions
Solution
‎03-19-2013 05:21 PM
Super User
Super User
Posts: 7,039

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

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;

View solution in original post


All Replies
Super User
Posts: 11,343

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

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.

Solution
‎03-19-2013 05:21 PM
Super User
Super User
Posts: 7,039

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

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;

Contributor
Posts: 50

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

Thanks Tom. That works.

Contributor
Posts: 50

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

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?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 225 views
  • 3 likes
  • 3 in conversation