PROC SQL - CREATE TABLE WITH MORE THAN ONE TABLE AND CONDITION WHERE

[Tecla1]

Goodmorning,

please I remember it's possible to have a PROC SQL with a condition WHERE who put data in two different files as here below:

PROC SQL

CREATE TABLE  white black;

a.*

…..

where color eq "white" or "black";

run;

Can you confirm and give me the right program format?

Tnks.

Tecla

[PeterClemmensen]

What is your goal here?

[Tecla1]

I have a table wher i put conditions wit where , at last my new table contains only data that replay to conditions and I loose every Others, I need to have a table with data that don't replay to where conditions….

Tnks!!!

[PeterClemmensen]

I think what you want to do is this. 

proc sql;
    create table white as
        select * from have
        where color eq 'white';
    create table black as
        select * from have
        where color eq 'black';
quit;

Or with a data step

data white black;
    set have;
    if color='white' then output white;
    else if color='black' then output black;
run;

[Tecla1]

Tnks for your kindly replay.

[Kurt_Bremser · Accepted solution]

SQL cannot create more than one table in a single action. You need to run a separate create table for each newly created table.

If you want to create more than one dataset from one existing dataset, SQL is the worst tool, use a data step instead, as you only need one pass through the input.

[Tecla1]

Tnks for your kindly replay !!!

[gamotte]

You can mark the thread as solved by accepting one of the answers.

Note that splitting datasets is rarely a good idea as SAS language is specifically designed to facilitate by group processing. What do you want to do that for ?