1

Assume we have the following table loaded as data source in SAS Visual Analytics:

=======================
Day | objectID | Value
1   |  1       | 1.5
1   |  2       | 1.5
1   |  3       | 3.2
2   |  1       | 1.5
2   |  2       | 1.5
3   |  1       | 1.5
3   |  4       | 4.3
=======================

Now I want to get a sum of all distinct objectIDs over these 3 days (assumption is that value does not change over days). literary, I would like to get:

value_for_objectID_1 + value_for_objectID_2 + value_for_objectID_3 + value_for_objectID_4

which would be in this case 1.5+1.5+3.2+4.3=10.5

In form of a SQL query:

select sum(a.value) from (select distinct objectID, value from DATA_TABLE_ABOVE) a;

Since to my knowledge, SAS Visual Analytics does not offer SQL queries within chosen data source in the "Report Designer", how would one be able to aggregate value conditionally (in this case over distinct values of another column). Any hints/suggestions would be very helpful.

kristof
  • 52,923
  • 24
  • 87
  • 110
Sale
  • 349
  • 1
  • 3
  • 15
  • I want to do the same thing ... I can't pre-process the data (which seems to the be standard response) because the data depends on the parameters chosen, and would lead to an infinite number of combinations. – user1420372 Jun 27 '17 at 04:34

1 Answers1

0

Try this:

proc sort data=have;
by objectid;
run;

data want;
   set have end=last;
   by objectid;
   retain sum;
   if first.objectid then sum+value;
   if last then output;
   keep sum;
run;
Shenglin Chen
  • 4,504
  • 11
  • 11
  • Thanks for the reply. However, the only place I could use the SAS Base code in SAS VIsual Analytics is the place where you prepare your data. IN the SAS Visual Analytics Report Designer part, only things I could choose are the calculated item and aggregated measure. – Sale Nov 10 '15 at 08:45