04-05-2018 03:58 PM - edited 04-05-2018 04:09 PM
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.
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?
04-05-2018 04:59 PM
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;
04-05-2018 05:05 PM
@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?
04-05-2018 09:36 PM
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