topic Re: Create 2 tables using Proc sql in SAS Programming

Create 2 tables using Proc sql

rsva — Tue, 10 Jul 2012 20:13:34 GMT

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.

Re: Create 2 tables using Proc sql

SASKiwi — Wed, 11 Jul 2012 02:19:55 GMT

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?

Re: Create 2 tables using Proc sql

TomKari — Wed, 11 Jul 2012 13:18:40 GMT

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

Re: Create 2 tables using Proc sql

rsva — Wed, 11 Jul 2012 14:47:13 GMT

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.

Re: Create 2 tables using Proc sql

Astounding — Wed, 11 Jul 2012 15:04:19 GMT

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.

Re: Create 2 tables using Proc sql

Howles — Thu, 12 Jul 2012 01:33:58 GMT

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.

Re: Create 2 tables using Proc sql

TomKari — Thu, 12 Jul 2012 14:28:40 GMT

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