0

I'm trying to select the first occurrence of record on two conditions but have been in vain. Here's my codes:

PROC SQL;

CREATE TABLE table1 AS
SELECT user_id, type, date, money 
FROM table2
WHERE date IN (SELECT MIN(date) 
FROM twice_transaction
GROUP BY user_id,type);

For example the original table looks like this(table2)

user  type  date      money
user1 type1 1/10/2012 money1
user1 type1 2/20/2012 money2
user1 type2 1/15/2012 money3
user1 type2 2/30/2012 money4
user2 type1 3/28/2012 money5
user2 type2 2/14/2012 money6
user2 type2 4/13/2012 money7

but I want only: (table1)

user1 type1 1/10/2012 money1
user1 type2 1/15/2012 money3
user2 type1 3/28/2012 money5
user2 type2 2/14/2012 money6

How should I modify/code for my end result? thanks!

M.Ali
  • 67,945
  • 13
  • 101
  • 127
ninaynt
  • 1
  • 1
  • 1
  • check this out http://stackoverflow.com/questions/17006765/get-a-the-row-number-in-a-data-step-using-sas – Pரதீப் Jan 02 '15 at 23:30
  • From an SQL standpoint, this is a classic [tag:greatest-n-per-group] problem. (well, "least" in this case, but the name is really referring to the "top" of something) – Clockwork-Muse Jan 03 '15 at 05:13
  • If you want to use the TOP or WINDOW function in SAS you'll need to use explicit SQL pass-through. – Reeza Jan 03 '15 at 05:54

2 Answers2

1

There are a couple of ways to do this with SQL. The comment by @NoDisplayName shows you a more traditional SAS way of accomplishing this.

CREATE TABLE table1 AS
SELECT a.user_id, a.type, a.date, a.money 
FROM table2 as a
INNER JOIN
SELECT (user_id, type, min(date) as date from table2 group by user_id, type) as b 
on a.user_id = b.user_id
and a.type = b.type
and a.date = b.date;

What I am doing here is creating an inner select to get the min date by user_id and type. Then I use an inner join to select only the records from the from the first table that line up with the second.

DomPazz
  • 12,415
  • 17
  • 23
1

Use the HAVING clause is also an option. 

data have;
informat usert type $8. date mmddyy10. money $8.;
format date date9.;
input usert  type  date      money;
cards;
user1 type1 1/10/2012 money1
user1 type1 2/20/2012 money2
user1 type2 1/15/2012 money3
user1 type2 2/28/2012 money4
user2 type1 3/28/2012 money5
user2 type2 2/14/2012 money6
user2 type2 4/13/2012 money7
;
run;

proc sql;
create table want as
select usert, type, date, money
from have
group by usert, type
having date=min(date);
quit;
Reeza
  • 20,510
  • 4
  • 21
  • 38
  • This isn't MySQL, you can't include columns not in a `GROUP BY` or an aggregate function (even if you could, `money` isn't necessarily guaranteed to contain the matching value from the given minimum-date row, although likelihood is really high). – Clockwork-Muse Jan 03 '15 at 05:12
  • @clockwork-Muse SAS allows inclusion of columns not in a GROUP BY and AFAIK does contain the matching value for the given minimum. – Reeza Jan 03 '15 at 05:26
  • SAS might, but it looks like you're telling it to run that particular SQL statement exactly, which SQL Server will cheerfully complain about. – Clockwork-Muse Jan 03 '15 at 05:41
  • It's in a PROC SQL block that isn't using explicit pass-through to the DB, so it's SAS SQL. If it was an explicit pass-through then it would be required to be valid SQL. – Reeza Jan 03 '15 at 05:51