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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.