Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Sum of Maximum values

Reply
Occasional Contributor
Posts: 9

Sum of Maximum values

[ Edited ]

My dataset has sales data for 3 years. For each year there are 3 records. I am interested in obtaining the sum of the maximum/ distinct values of the 3 years. 

 

YearSales
201619959
201619959
201619959
20159201
20159201
20159201
2017306245
2017306245
2017306245

 

Desired Result: 19959+9201+306245 = 335405

 

I have tried the following techniques to no avail:

 

Technique 1: Sum (by GRoup) [Max( by Group)(Sales)] 

This resulted in an error as we can not perform an aggregate on an aggregate

 

Technique 2: Duplicated sales variable and changed the default aggregate measure to MAX. Following which I ran a sum on Sales. 

 

Achieved Result: 1006215

 

Does anyone know of a way I can obtain my desired result? 

PROC Star
Posts: 631

Re: Sum of Maximum values

Using sub-queries can get you this result

data have;
infile datalines delimiter='09'x;
input Year	Sales;
datalines;
2016	19952
2016	19955
2016	19959
2015	9200
2015	9111
2015	9201
2017	306200
2017	306244
2017	306245
;
run;

proc sql;
create table want as 
select a.Year,a.Sales,MAX(a.Sales) as max_Group,b.SUM_MAX_Val
	from have a, (select SUM(MAX_Val) as SUM_MAX_Val
						from (select distinct MAX(Sales) as MAX_Val 
									from have 
									group by year) 
								) b
						
group by a.year
;
quit;
Thanks,
Suryakiran
Super User
Posts: 2,068

Re: Sum of Maximum values

@don_data I have no clue of SAS visual analytics. Are you looking for a programming solution despite posting in visual analytics board? Or does visual analytics also has a user written facility?

Occasional Contributor
Posts: 9

Re: Sum of Maximum values

Posted in reply to novinosrin

I am looking at doing this in SAS Visual Analytics. 

 

Unfortunately, to my knowledge, the Visual Analytics module does not allow for proc codes.  

PROC Star
Posts: 631

Re: Sum of Maximum values

Using Data Builder (Data preparation) you can use custom code. When you open a data query and start initial Design then go to CODE tab select All Code. There you can see a lock icon, click this to unlock the code for manual editing. 

Capture.PNG

 

For more information refer to SAS Visual Analytics User Guide 7.4 click here

Thanks,
Suryakiran
Super User
Posts: 4,030

Re: Sum of Maximum values

Add a new column to your data as below then just sum that:

 

Year Sales_Unique
2016 19959
2016 0
2016 0
2015 9201
2015 0
2015 0
2017 306245
2017 0
2017 0
Ask a Question
Discussion stats
  • 5 replies
  • 158 views
  • 0 likes
  • 4 in conversation