Using proc SQL to keep obs based on multiple column criteria

[Thoney]

Hi All,

I have never used this forum before so just wanted to firstly introduce myself, but also apologise in case I have accidentally broken any forum rules or asked a question that has already been answered!

Basically I am quite new to SAS and still in the learning beginner phase. Imagine for example I have a record set where I have account numbers that may or may not appear more than once, and a second column with Dates in. Using Proc SQL, (or any other method you see fit) how would I keep only account numbers with the maximum date for each account number? So for each unique account number in column a, keep only the record containing the highest date value in column b. My experience is mainly in VBA, so understanding a simple step like this might help me to come across to the syntax of SAS, as all I'm thinking about right now is some sort of For Each statement!

Many thanks in advance,

Tom.

[PGStats · Accepted solution]

Welcome to the Forum. This will create a new dataset of all records where the date is the latest date for an account number (it can be many records if the latest date appears more than once) :

proc sql;

create table lastDates as

select *

from myDataSet

group by actNumber

having date=max(date);

quit;

PG

[Linlin]

Hi,

Welcome to the forum.

Below is a sample code.

data have;

input account date mmddyy10.;

format date mmddyy10.;

cards;

1001 10/23/2008

1001 10/28/2009

1005 09/19/2008

1006 08/08/2008

1006 09/09/2009

1006 10/10/2010

1007 12/12/2012

;

proc sql;

  create table want as

    select account,max(date) as max_date format=mmddyy10.

      from have

       group by account;

quit;

proc print data=want;run;

[art297]

and here are a couple of non-sql ways to do what you want:

data have;

  informat id $3.;

  informat date date.;

  format date date.;

  input id date;

  cards;

001 10jan2012

001 31dec2012

001 5mar2012

002 30dec2012

002 29dec2012

;

/*using most other methods*/

proc sort data=have;

  by id descending date;

run;

/*using sort after sorting*/

proc sort data=have out=want nodupkey;

  by id;

run;

/*using datastep after sorting*/

data want;

  set have;

  by id;

  if first.id then output;

run;

You can also do it using proc summary, using a hash in a datastep and probably a number of other ways I'm not thinking of at the moment.

[Linlin]

Hi Art,

Happy New Year to you! How are you doing? Is it cold in your area? ours is about 20F(-5C) today.

[art297]

: Sounds like Boston and Toronto are going to be equally cold tonight.  Time to make good use of my fireplace.

Happy New Year to you too!

[Thoney]

Hi All,

Just wanted to thank you all for your swift responses, I have found exactly what I needed and several other ways of achieving the same goal! Very pleased!!

Tom.

[TomKari]

Hi, Tom

If you're fairly new to SAS, you should check out a SAS product called Enterprise Guide. It provides a point and click interface that generates the SAS code, so you can use EG to obtain your result, and then see what SAS code was created to do it.

Tom

[Thoney]

Hi Tom

This is something that I asked one of my colleagues when I first started, however it appears that the company I work for are not renewing the part of the license that covers Enterprise. Therefore I've been told I will only be able to use raw SAS code, so must learn the "in at the deep end" way lol. Thanks for the suggestion though

Tom