Hi I have a large dataset consisting of millions of rows, I am trying to bring back the Ref minimum of post_date and minimum of from_date. what is the best way this can be done? See example in table below.
Table_Test | |||
Ref | Post_Date | From_Date | To_Date |
1 | 01/01/2017 | 27/12/2016 | 06/01/2017 |
1 | 01/01/2017 | 07/01/2017 | 17/01/2017 |
1 | 02/02/2017 | 18/01/2017 | 28/01/2017 |
2 | 01/02/2016 | 27/01/2016 | 01/02/2016 |
2 | 01/02/2016 | 02/02/2016 | 07/02/2016 |
2 | 03/04/2016 | 08/02/2016 | 13/02/2016 |
2 | 03/05/2016 | 14/02/2016 | 19/02/2016 |
3 | 10/04/2015 | 05/04/2015 | 15/04/2015 |
3 | 11/09/2015 | 06/09/2015 | 16/09/2015 |
4 | 04/02/2014 | 30/01/2014 | 09/02/2014 |
4 | 04/02/2016 | 10/02/2014 | 20/02/2014 |
4 | 01/01/2017 | 27/12/2016 | 06/01/2017 |
Method 1: SQL
proc sql;
create table want as
select ref, min(post_date) as min_post_date, min(from_date) as min_from_date
from have
group by ref
;
quit;
Method 2: data step
proc sort data=have;
by ref;
run;
/* omit this step if have is already sorted, as in your example */
data want (keep=ref min_post_date min_from_date);
set have;
by ref;
retain min_post_date min_from_date;
format min_post_date min_from_date ddmmyy10.;
if first.ref
then do;
min_post_date = '31dec9999'd;
min_form_date = '31dec9999'd;
end;
min_post_date = min(min_post_date,post_date);
min_from_date = min(min_from_date,from_date);
if last.ref then output;
run;
Method 3: proc means
proc means data=have noprint;
by ref;
var post_date from_date;
output
out=want (drop=_type_ _freq_)
min(post_date)=min_post_date
min(from_date)=min_from_date
;
run;
/* also needs sorting */
If the cardinality of ref is sufficiently low, you can use class instead of by in the proc means, and avoid sorting.
Method 1: SQL
proc sql;
create table want as
select ref, min(post_date) as min_post_date, min(from_date) as min_from_date
from have
group by ref
;
quit;
Method 2: data step
proc sort data=have;
by ref;
run;
/* omit this step if have is already sorted, as in your example */
data want (keep=ref min_post_date min_from_date);
set have;
by ref;
retain min_post_date min_from_date;
format min_post_date min_from_date ddmmyy10.;
if first.ref
then do;
min_post_date = '31dec9999'd;
min_form_date = '31dec9999'd;
end;
min_post_date = min(min_post_date,post_date);
min_from_date = min(min_from_date,from_date);
if last.ref then output;
run;
Method 3: proc means
proc means data=have noprint;
by ref;
var post_date from_date;
output
out=want (drop=_type_ _freq_)
min(post_date)=min_post_date
min(from_date)=min_from_date
;
run;
/* also needs sorting */
If the cardinality of ref is sufficiently low, you can use class instead of by in the proc means, and avoid sorting.
Thanks for your help Kurt, much appreciated
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.