Date:         Wed, 12 Feb 2003 12:15:22 -0800
Reply-To:     Mark Terjeson <mark.terjeson@NWCSR.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mark Terjeson <mark.terjeson@NWCSR.COM>
Subject:      Re: SQL
Comments: To: "Chen, Jian" <ozz6@CDC.GOV>
In-Reply-To:  <707F3F5DDB6BD511A3CF0008C7863E2305A39BE1@mcdcatl41>
Content-Type: text/plain; charset="us-ascii"

Hi,



If you have only one shot_date per id:
======================================

Ya gave you a good SQL version for your case.

This case is a good candidate for first. last.
processing.  So here is the equivalent in a
datastep version:


data Table1;
    input Name  $  id    birth_date    shot_date;
cards;
Joe     12    20010912       20011012
Sopjie  9     20001010       20010101
Joe     12    20010912       .
John    4     19990909       .
Joe     12    20010912       20011012
John    4     19990909       .
;
run;

proc sort data=Table1 out=Table2;
    by id shot_date;
run;

data Table3;
    set Table2;
    by id;
    if last.id;
run;




However, if you possibly have more than one shot_date
per id, you will have to do a little more in the datastep
and a lot more in SQL.  Here is a datastep version:
=====================================================


data Table1;
    input Name  $  id    birth_date    shot_date;
cards;
Joe     12    20010912       20011012
Sopjie  9     20001010       20010101
Joe     12    20010912       .
John    4     19990909       .
Joe     12    20010912       20011012
Joe     12    20010912       20011013
John    4     19990909       .
;
run;

proc sort data=Table1 out=Table2 nodupkey;
    by id shot_date;
run;

data Table3;
    set Table2;
        by id shot_date;
        if first.id and last.id then
            do;
                output;
            end;
        else
            do;
                if shot_date ne .
                and last.shot_date then output;
            end;
run;



Hope this is helpful,
Mark Terjeson
Northwest Crime and Social Research, Inc.
A SAS Alliance Partner
215 Legion Way SW
Olympia, WA   98501
360.870.2581 - voice,cell
360.570.7533 - fax
mailto:mark.terjeson@nwcsr.com
www.nwcsr.com

"Nothing is particularly hard
if you divide it into small jobs."
- Henry Ford, Industrialist




-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Chen, Jian
Sent: Wednesday, February 12, 2003 11:51 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL


Dear SASers:

How can I use proc sql to change the following dataset:

Name    id    birth_date    shot_date
Joe     12        20010912       20011012
Sopjie  9     20001010       20010101
Joe     12    20010912
John    4     19990909
Joe     12    20010912       20011012
John    4         19990909

Where names are not unique while id is unique. Some records have null
values
for variable shot_date but other records for the same id may have values
for
shot_date. I need records to have shot_date if that exists. I want each
record with unique id and delete any duplicates.

The data set I want should look like:

Name    id   birth_date     shot_date
Joe     12        20010912       20011012
Sopjie  9     20001010       20010101
John    4     19990909

Thanks a lot!