2

This should be fairly simple and thanks in advance. I have a unique ID column and a start column populated with integers. I want to create a new column populated with the minimum start date for each unique ID. example follows:

ID START

1 23

1 24

1 34

2 12

2 11

and what i want

ID START minStart

1 23 23

1 24 23

1 34 23

2 12 11

2 11 11

user2448666
  • 329
  • 1
  • 6
  • 14

3 Answers3

4

SAS proc sql has a facility called re-merging that allows you to do this in one step:

proc sql;
    select id, start, min(start)
    from t
    group by id;
run;

SAS recognizes a group by where not all the non-aggregated columns are included in the group by clause. In this case, it returns each row in the original data set, with the aggregation function min(start) aggregated according to id (because it is in the group by) clause.

Garf
  • 75
  • 1
  • 12
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Does MySQL do the same? – Hart CO Aug 05 '13 at 13:30
  • @GoatCO . . . Absolutely not. MySQL chooses an arbitrary row but does the aggregation. SAS aggregates the values, but returns the original rows. By the way, more recent versions of the ANSI standard actually do allow for non aggregated columns in the `group by` -- these are intended for primary keys of reference tables, so you can simply access the columns. Postgres also offers a syntax similar to MySQL (and consistent with the standard). – Gordon Linoff Aug 05 '13 at 13:32
  • ah, thanks, I knew MySQL had some differences in `GROUP BY` than T-SQL but haven't spent much time with it. – Hart CO Aug 05 '13 at 13:36
  • SAS keeps crashing on my p.o.s department funded school computer when the remerging occurs. Can I just make a new table with these three columns and ill merge them later? – user2448666 Aug 05 '13 at 14:49
0

In T-SQL this would do it:

SELECT a.ID, a.START, b.MinStart
FROM Table AS a
JOIN (SELECT ID, MIN(START)'MinStart'
      FROM Table
      GROUP BY ID
     )AS b
ON a.ID = b.ID

But it looks like there's a better method in SAS per Gordon's answer.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • You create `MinStart` in the subquery aliased as table b, so it's available in the outside query. It's not TableA and TableB, just the same table aliased as `a` and then again in the subquery aliased as `b`. I'll make the aliases more clear with `AS`. – Hart CO Aug 05 '13 at 13:35
0

TRY THIS 

SELECT A.ID,A.START,B.START FROM TABLE_NAME A
 INNER JOIN
 (
      SELECT ID ,MIN(START) START FROM TABLE_NAME
      GROUP BY ID
 )B
ON A.ID=B.ID

Update me if not

Regards

Ashutosh Arya

Ricardo
  • 96
  • 4
Ashutosh Arya
  • 1,138
  • 2
  • 8
  • 14