topic Aggregating by a variable in SAS Programming

Aggregating by a variable

Adnan_Razaq — Wed, 16 May 2018 13:02:47 GMT

Hi All,

I have the dataset below which gives me details of account conversions at a sub account level.

data have;
	input account_id $ Sub_no $ reporting_date conversion_date;
	informat reporting_date conversion_date date9.;
	format reporting_date conversion_date date9.;
	
	cards;
	12345 1 31JAN2018 .
	12345 2 31JAN2018 .
	12345 3 31JAN2018 .
	67891 1 31JAN2018 .
	67891 2 31JAN2018 .
	12345 1 28FEB2018 28FEB2018
	12345 2 28FEB2018.
	12345 3 28FEB2018 .
	67891 1 28FEB2018 .
	67891 2 28FEB2018 28FEB2018
	;
run;

What I need is to aggregate this data to show the below

Account_ID	Reporting Date	Conversion_Date
12345	31-Jan-18	.
67891	31-Jan-18	.
12345	28-Feb-18	28-Feb-18
67891	28-Feb-18	28-Feb-18

I need to view 1 record per account number and reporting date irrespective of number of sub accounts the data has been aggregated from.

Many Thanks

Adnan

Re: Aggregating by a variable

PaigeMiller — Wed, 16 May 2018 13:08:22 GMT

PROC SUMMARY will do this

UNTESTED CODE

proc summary data=have;
    class account_id;
    var reporting_date conversion_date;
    output out=want min=;
run;

Re: Aggregating by a variable

Kurt_Bremser — Wed, 16 May 2018 13:15:12 GMT

Two approaches, SQL and data step, that produce the same result:

data have;
input account_id $ Sub_no $ reporting_date conversion_date;
informat reporting_date conversion_date date9.;
format reporting_date conversion_date date9.;
cards;
12345 1 31JAN2018 .
12345 2 31JAN2018 .
12345 3 31JAN2018 .
67891 1 31JAN2018 .
67891 2 31JAN2018 .
12345 1 28FEB2018 28FEB2018
12345 2 28FEB2018.
12345 3 28FEB2018 .
67891 1 28FEB2018 .
67891 2 28FEB2018 28FEB2018
;
run;

proc sql;
create table want1 as
select account_id, reporting_date, max(conversion_date) as conversion_date format=date9.
from have
group by reporting_date, account_id;
quit;

data want2;
set have (rename=(conversion_date=_cvdate));
by reporting_date account_id;
retain conversion_date;
format conversion_date date9.;
if _cvdate ne . then conversion_date = _cvdate;
if last.account_id then output;
drop _cvdate sub_no;
run;