DATA Step, Macro, Functions and more

Aggregating by a variable

Accepted Solution Solved
Reply
Highlighted
Contributor
Posts: 24
Accepted Solution

Aggregating by a variable

 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_IDReporting DateConversion_Date
1234531-Jan-18.
6789131-Jan-18.
1234528-Feb-1828-Feb-18
6789128-Feb-1828-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


Accepted Solutions
Solution
a week ago
Super User
Posts: 9,574

Re: Aggregating by a variable

Posted in reply to Adnan_Razaq

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Respected Advisor
Posts: 2,650

Re: Aggregating by a variable

Posted in reply to Adnan_Razaq

PROC SUMMARY will do this

 

UNTESTED CODE

proc summary data=have;
    class account_id;
    var reporting_date conversion_date;
    output out=want min=;
run;
--
Paige Miller
Solution
a week ago
Super User
Posts: 9,574

Re: Aggregating by a variable

Posted in reply to Adnan_Razaq

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 70 views
  • 0 likes
  • 3 in conversation