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

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

Macro — Tue, 19 Mar 2013 20:18:01 GMT

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?

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

ballardw — Tue, 19 Mar 2013 20:34:59 GMT

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.

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

Tom — Tue, 19 Mar 2013 21:21:30 GMT

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;

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

Macro — Thu, 21 Mar 2013 17:05:17 GMT

Thanks Tom. That works.

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

Macro — Thu, 21 Mar 2013 17:14:22 GMT

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?