topic How to select mind date per each unique ID in SAS Enterprise Guide

How to select mind date per each unique ID

Dogo23 — Mon, 18 Dec 2017 16:21:53 GMT

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.

Re: How to select mind date per each unique ID

Reeza — Mon, 18 Dec 2017 16:27:20 GMT

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;

Re: How to select mind date per each unique ID

Reeza — Mon, 18 Dec 2017 16:28:14 GMT

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.