Date:         Thu, 26 Jul 2001 13:53:39 -0700
Reply-To:     "Terjeson, Mark" <TerjeMW@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <TerjeMW@DSHS.WA.GOV>
Subject:      Re: SQL question
Comments: To: Poul Ravn Sxrensen <poulravn@POST3.TELE.DK>
Content-Type: text/plain; charset=us-ascii

Hi Poul,

Of course this can be done in either datastep
or SQL, but here's an SQL way to do this:

data table1;
    timestamp=1311773177; a='1'; output;
    timestamp=1311773178; a='2'; output;
    timestamp=1311773179; a='3'; output;
    timestamp=1311773180; a='4'; output;
    timestamp=1311773181; a='5'; output;
    timestamp=1311773182; a='6'; output;
run;

data table2;
    timestamp=1311773178; a='2'; output;
    timestamp=1311773180; a='4'; output;
    timestamp=1311773182; a='6'; output;
run;


    * finding leftover entire rows ;
    * that are in table1 and not in table2 ;
proc sql;
    create table inTable1only as
    select * from table1
    except all
    select * from table2;
quit;


If using version 6, truncate the sample names I have
given to something of length 8 or less.

Hope this is helpful,
Mark Terjeson
Washington State Department of Social and Health Services
Division of Research and Data Analysis (RDA)
mailto:terjemw@dshs.wa.gov


-----Original Message-----
From: Poul Ravn Sxrensen [mailto:poulravn@POST3.TELE.DK]
Sent: Thursday, July 26, 2001 12:44 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL question


Hi there, thanks for help in the past. I hope I contributed also to other
peoples problems.

Here is  another question:

On a remote server a SAS session runs continously. Big datasets are created.
Sometimes there is a need to extract part of the data to local datasets. I
need to find a way to only extract data that I have not already extracted.
The data is arranged chronologically on the server but there are data with
the same timestamps. I would like to able to do a SQL-query that
'extract-excludes', i.e. DOES NOT extract the data I already have. Probably
there is no way to do this (thats my feeling) but I wanted to hear your
opinion.

I am thinking alon the lines of 'merge data1(in= ...) data2(in=..) followed
by a conditional selection of the data where only data2 contributed.

Regards

Poul ravn Sorensen