topic Re: Sum of Maximum values in SAS Visual Analytics

Sum of Maximum values

don_data — Thu, 05 Apr 2018 20:09:36 GMT

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?

Re: Sum of Maximum values

SuryaKiran — Thu, 05 Apr 2018 20:59:39 GMT

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;

Re: Sum of Maximum values

novinosrin — Thu, 05 Apr 2018 21:05:57 GMT

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

Re: Sum of Maximum values

don_data — Thu, 05 Apr 2018 21:37:34 GMT

I am looking at doing this in SAS Visual Analytics.

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

Re: Sum of Maximum values

SuryaKiran — Fri, 06 Apr 2018 01:36:14 GMT

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

Re: Sum of Maximum values

SASKiwi — Fri, 06 Apr 2018 22:49:14 GMT

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

Re: Sum of Maximum values

don_data — Fri, 07 Dec 2018 20:35:28 GMT

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

Re: Sum of Maximum values

SASKiwi — Sat, 08 Dec 2018 20:28:28 GMT

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