BookmarkSubscribeRSS Feed
don_data
Fluorite | Level 6

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. 

 

YearSales
201619959
201619959
201619959
20159201
20159201
20159201
2017306245
2017306245
2017306245

 

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? 

7 REPLIES 7
SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran
novinosrin
Tourmaline | Level 20

@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
Fluorite | Level 6

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
Meteorite | Level 14

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. 

Capture.PNG

 

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

Thanks,
Suryakiran
don_data
Fluorite | Level 6

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

SASKiwi
PROC Star

@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
PROC Star

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 7 replies
  • 2803 views
  • 0 likes
  • 4 in conversation