Desktop productivity for business analysts and programmers

How to select mind date per each unique ID

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

How to select mind date per each unique ID

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. 


Accepted Solutions
Solution
‎12-18-2017 12:00 PM
Super User
Posts: 22,875

Re: How to select mind date per each unique ID

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;

View solution in original post


All Replies
Solution
‎12-18-2017 12:00 PM
Super User
Posts: 22,875

Re: How to select mind date per each unique ID

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;
Super User
Posts: 22,875

Re: How to select mind date per each unique ID

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. 


 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 178 views
  • 0 likes
  • 2 in conversation