BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Thoney
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

8 REPLIES 8
PGStats
Opal | Level 21

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

PG
Linlin
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Lapis Lazuli | Level 10

Hi Art,

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

art297
Opal | Level 21

: 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
Calcite | Level 5

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

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
Calcite | Level 5


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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1071 views
  • 6 likes
  • 5 in conversation