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.
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;
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.