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?
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;
@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?
I am looking at doing this in SAS Visual Analytics.
Unfortunately, to my knowledge, the Visual Analytics module does not allow for proc codes.
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
Thanks SuryaKiran. However, I do not think this flexibility/ option is available in version 8.2 of SAS VA.
@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.
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.