BookmarkSubscribeRSS Feed
rsva
Fluorite | Level 6


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.

6 REPLIES 6
SASKiwi
PROC Star

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
Fluorite | Level 6

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
PROC Star

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
Onyx | Level 15

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
Onyx | Level 15

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
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 7703 views
  • 0 likes
  • 5 in conversation