Sum of Maximum values

[don_data]

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. 

Year	Sales
2016	19959
2016	19959
2016	19959
2015	9201
2015	9201
2015	9201
2017	306245
2017	306245
2017	306245

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? 

[SuryaKiran]

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;

[novinosrin]

@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?

[don_data]

I am looking at doing this in SAS Visual Analytics. 

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

[SuryaKiran]

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. 

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

[don_data]

Thanks SuryaKiran. However, I do not think this flexibility/ option is available in version 8.2 of SAS VA. 

[SASKiwi]

@don_data - you can use normal SAS code outside of VA when you are preparing the data to be loaded into your VA server. The interface to build your code could be SAS Studio which comes bundled with SAS VA.

[SASKiwi]

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