CODEWITHSUNDEEP
sqlsas
user2146441
user2146441

Reputation: 228

Get latest change of variable according to a timestamp

I have the following data:

data have;
   input username $  betdate : datetime. customerCode;
   dateOnly = datepart(betdate) ;
   format betdate DATETIME.;
   format dateOnly ddmmyy8.;
   datalines; 
    player1 12NOV2008:12:04:01 1
    player1 04NOV2008:09:03:44 10
    player2 07NOV2008:07:03:33 1 
    player2 05NOV2008:09:00:00 0.5 
    player3 05NOV2008:09:05:00 1
    player2 07NOV2008:14:03:33 1 
    player1 05NOV2008:09:00:05 20
    player2 07NOV2008:16:03:33 1 
    player2 07NOV2008:18:03:33 1 
    player2 09NOV2008:10:05:10 0.7
    player3 15NOV2008:15:05:33 10 
    player3 15NOV2008:15:05:33 1
    player2 15NOV2008:15:05:33 0.1
run;
PROC PRINT; RUN;

How can I run a 'proc sql' command to pull out each player's latest (i.e. the distinct(customerCode) with the latest date (i.e: max(betdate) and each player's earliest (by betdate again) update for customerCode? This is a dynamic variable that's changing from time to time? Do I have to run a subselect for each username, looking for the max(betdate) and customerCode from the table?

Thanks.

Upvotes: 1

Views: 208

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use a feature of proc sql known as aggregation with "remerging". The following query outputs every row with the max date by username:

proc sql;
 select H.username, H.customercode, date, max(date) as maxdate
 from have H 
 group by H.username
quit;

You can then put this in a subquery to get the values you want:

proc sql;
 select username, customercode, date
 from (select H.username, H.customercode, date, max(date) as maxdate
       from have h
       group by H.username
      ) h
 where date = maxdate;
quit;

The following may also work, but I don't have SAS on hand to test where I am now:

proc sql;
  select H.username, H.customercode, date
  from have H 
  group by H.username
  having date = max(date)
quit;

Upvotes: 1

Joe
Joe

Reputation: 63424

PROC SQL isn't the easiest way to do it, but it's certainly possible.

proc sql;
 select H.username, H.customercode from have H inner join (
  select username, max(betdate) as maxdate from have group by username
 ) V
 on H.username=V.username and H.betdate=V.maxdate;
quit;

That returns two values for Player3, you would have to work out how to resolve ties. Data step is much easier though, requiring at most one sort and one pass (and in a hash would be even faster, or in an IML matrix).

Upvotes: 1

Related Questions