How to select mind date per each unique ID

[Dogo23]

Hello

I want to take the earlier occurrence of each unique ID from a table I created. As of right now, if I say min (date), I am the earliest single record coming from that table and it is applied to each row in the new table. Instead, I wanted the earliest date attributed to each of those distinct ID's. 

proc sql;
create table t2 as
select
distinct campaign_id,
min (datepart(date)) as processed_dttm format= mmddyy8.,
total_sent,
open,
click,
from t2;
quit;

Doing this, even though I asked for "distinct" ID, still applies the earliest single date (11/06/16) to every record in the new table. 

[Reeza · Accepted solution]

Add a GROUP BY so the analysis is done by the GROUP variables. DISTINCT isn't what you're looking for in this case.

proc sql;
create table t2 as
select campaign_id,
min (datepart(date)) as processed_dttm format= mmddyy8.,
total_sent,
open,
click,
from t2
gorup by campaign_id;
quit;

[Reeza]

Note that you haven't applied aggregate functions to the remaining variables so you're still going to end up with your full table but a new variable that identifies the min date. This isn't the same behaviour you'd experience in a DBMS version of SQL.

---

@schlotty23 wrote:

Hello

I want to take the earlier occurrence of each unique ID from a table I created. As of right now, if I say min (date), I am the earliest single record coming from that table and it is applied to each row in the new table. Instead, I wanted the earliest date attributed to each of those distinct ID's. 

proc sql;
create table t2 as
select
distinct campaign_id,
min (datepart(date)) as processed_dttm format= mmddyy8.,
total_sent,
open,
click,
from t2;
quit;

Doing this, even though I asked for "distinct" ID, still applies the earliest single date (11/06/16) to every record in the new table. 

---