Create 2 tables using Proc sql

[rsva]

Hi,

I am trying to use Proc Sql to create 2 tables at a time. Below is my sample code in data step. Any suggestions would help.

Data Match NoMatch;

     Merge One (in=a) Two (in=b);

     By id;

     if a*b then output Match;

     else if a=1 and b ne 1 then output NoMatch;

     else delete;

Run;

Thanks.

[SASKiwi]

Unfortunately I think it is not possible within the same SQL statement:

create table example as select from .......;

You could do it in two SQL statements but that means passing through your data twice.

One of the advantages of the DATA step is the ability to create multiple tables while processing your input data (datasets One and Two) only once. Why not stick with your sample code?

[rsva]

Thanks SASKiwi and TomKari. I wanted to see if I can use Proc Sql to avoid sorting and renaming the variables since I have to create quite a few data (Match and NoMatch) and then merge with 2 other data. Just wanted to explore the option. Anyway it's good to know that we do not have an option to do in Proc SQL. BUT it would be nice to have it since I have had to do this on several occasions.

Aruna.

[Astounding]

By any chance, does one of your data sets contain just the variable ID?  If you are only looking to determine matches/mismatches with a master list of IDs, and not bringing in other variables, there are plenty of other ways to go about it (formats, hashing).  And a DATA step will let you process tons of data sets in the same step if that is the case.

[TomKari]

One important point; although you don't add code in a SQL Join to sort the data, in any non-trivial application the data wil be sorted behind the scenes to enable the merge. I've seen cases where people think they save resources by eliminating one or more sorts, and this is absolutely not the case.

Tom

[TomKari]

I can confirm SASKiwi's comment. I researched this for a client once, and you can't create two tables from one SQL select statement.

In this case, your DATA step is the best solution, unless there's another reason for wanting to use SQL?

Tom

[Howles]

There is an "almost" solution which will allow the logic to remain in SQL.

See http://www.sascommunity.org/wiki/CREATE_statement_(SQL)_can_generate_only_one_output

rsva wrote:


Hi,

I am trying to use Proc Sql to create 2 tables at a time. Below is my sample code in data step. Any suggestions would help.

Data Match NoMatch;
     Merge One (in=a) Two (in=b);
     By id;
     if a*b then output Match;
     else if a=1 and b ne 1 then output NoMatch;
     else delete;
Run;

Thanks.