DATA Step, Macro, Functions and more

Max/Min date function on a variable

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Max/Min date function on a variable

Hi,

I have a transactional dataset that I want to roll-up to the contact level.
example:
id date sales
1 01Jan11 12
1 02Jan11 20
2 12Jan11 34
2 23Feb11 21
etc..

the rolled up set would look like
id earliestdate sales_sum
1 01Jan11 32
2 12Jan11 55
etc.

the trouble I am having is outputing the 'earliestdate' as the end of my datastep is if last.id then output ;

Thanks for your help.

Accepted Solutions
Solution
‎11-09-2016 03:38 PM
Contributor
Posts: 24

Re: Max/Min date function on a variable

[ Edited ]
Posted in reply to Danglytics


Editor's Note:  The solution provided by buckeye will allow you to total up SALES while capturing the earliest DATE.  Because a SAS date is a numeric value, the MIN function can be used to capture the earliest date.  The DATA step version of buckeye's PROC SQL code is show below as well.  Please note that in order for the DATA step version to work, the data set will have to be sorted by ID and DATE.

       

 

 

 


 PROC SQL;
 CREATE TABLE mySum AS
 SELECT ID,MIN(date) AS Earliest, SUM(sales) AS SalesTot FROM salesrecord GROUP BY ID;
 QUIT;



/*DATA Step version of the above code*/

data a; 
input id date : date9. sales; 
datalines; 
1 01Jan11 12
1 02Jan11 20
2 12Jan11 34
2 23Feb11 21
;

data b; 
set a; 
by id; 
retain earliest; 
if first.id then do; 
earliest=date;
 sales_sum=0; 
end; 
sales_sum+sales;
if last.id then output; 
drop sales date; 
run; 

proc print; 
format earliest date9.; 
run;

 

 

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 3,174

Re: Max/Min date function on a variable

Posted in reply to Danglytics
Share what SAS code you have created thus far to solve the problem/objective. You should be using a RETAIN statement to capture / retain a "temporary date variable" and then on LAST. you will want to output.

Otherwise why not use PROC SUMMARY/MEANS with the MIN function instead?

Scott Barry
SBBWorks, Inc.
Contributor
Posts: 66

Re: Max/Min date function on a variable

This is an overly simplified example of the actual data i'm using, and for that reason theproc summary / means wouldnt work, so i was hoping to incorporate some sort code to my data step.

but in this case i would do

data rollup ;
set transactions ;
by id ;
if first.id then do sales_sum=. ;
sales_sum + sales ;
if last.id then output ;
run ;

i havent been able to figure out capturing the earliest transaction date part of the code yet.
Contributor
Posts: 66

Re: Max/Min date function on a variable

Posted in reply to Danglytics
I think ive figured it out.

data rollup ;
set transactions ;
by id ;
retain earliestdate ;
if first.id then do;
sales_sum=. ;
earliestdate= '01jan3000'd *set date to sometime way in the future, i know probably not the best practice.. ;
end;
earliestdate = min(earliestdate,date) ;
sales_sum + sales ;
if last.id then output ;
run ;
Super Contributor
Super Contributor
Posts: 3,174

Re: Max/Min date function on a variable

Posted in reply to Danglytics
Slight coding simplification, if you have FIRST.ID, then you can assign directly the DATE and SALES variables to capture it without using MIN function. Also eliminates need to set high values and/or MISSING value.

Honestly, you can accomplish this processing with PROC SUMMARY/MEANS -- have a look at the DOC.

Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,177

Re: Max/Min date function on a variable

Posted in reply to Danglytics
Danglytics
like the approach : starting with the "earliest" at a "high value"
Going a little further lets the SAS system indicate the missing with ****
The date constants allow for a year like 20000 (count those zeroes)
This SASlog indicates a change when the year grows larger than 20000[pre]19 data ;
20 retain earliest '31dec20000'd ;
21 do date= earliest to earliest +1 ;
22 put date date11. ;
23 put date date9. ;
24 put date ddmmyy10. ;
25 end ;
26 stop ;
27 run;

31-DEC-****
31DEC****
31/12/****
***********
*********
**********
NOTE: The data set [/pre] So, for a high-value date, I would recommend the number 6589336
or (my preference) the syntax
%sysevalf( "31dec20000"d +1 )
Solution
‎11-09-2016 03:38 PM
Contributor
Posts: 24

Re: Max/Min date function on a variable

[ Edited ]
Posted in reply to Danglytics


Editor's Note:  The solution provided by buckeye will allow you to total up SALES while capturing the earliest DATE.  Because a SAS date is a numeric value, the MIN function can be used to capture the earliest date.  The DATA step version of buckeye's PROC SQL code is show below as well.  Please note that in order for the DATA step version to work, the data set will have to be sorted by ID and DATE.

       

 

 

 


 PROC SQL;
 CREATE TABLE mySum AS
 SELECT ID,MIN(date) AS Earliest, SUM(sales) AS SalesTot FROM salesrecord GROUP BY ID;
 QUIT;



/*DATA Step version of the above code*/

data a; 
input id date : date9. sales; 
datalines; 
1 01Jan11 12
1 02Jan11 20
2 12Jan11 34
2 23Feb11 21
;

data b; 
set a; 
by id; 
retain earliest; 
if first.id then do; 
earliest=date;
 sales_sum=0; 
end; 
sales_sum+sales;
if last.id then output; 
drop sales date; 
run; 

proc print; 
format earliest date9.; 
run;

 

 

Occasional Contributor
Posts: 14

Re: Max/Min date function on a variable

Posted in reply to Danglytics
Friend,

I hope the below code will suffix your problem, if not please do update me to make corrections

data test;
input id date date7. sales;
format date date7.;
datalines;
1 01Jan11 12
1 02Jan11 20
2 12Jan11 34
2 23Feb11 21
;
proc sort;
by id descending date;
run;
data test1;
set test;
by id;
if first.id then total_sales=0;
total_sales+sales;
if last.id;
run;
proc print;
run;
🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 22437 views
  • 0 likes
  • 5 in conversation