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?
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;
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
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.