Using VA 7.5 under SAS 9.4 I'm looking for a solution that avoids double counting (sums) when aggregating data.
What I've got is data like below where object A appears under two different Lev3 categories.
data have;
infile datalines truncover dsd;
input (lev1 lev2 lev3 object) ($) size;
datalines;
L1,L2.1,L3.1,A,5
L1,L2.1,L3.1,b,3
L1,L2.1,L3.2,A,5
L1,L2.1,L3.2,c,1
;
What I want is a VA report where I can drill down into the hierarchies. If I look at Lev3 detail then I want to see the size of object A as in the data, but if I look at Lev2 then I want the aggregated size value to only include object A once.
To illustrate what I'm after here how I could do this with a SQL
%macro aggregation(categories);
proc sql;
select
&categories.,
sum(size) as sum_size
from
(select distinct &categories., object,size from have)
group by &categories
;
quit;
%mend;
%aggregation(%str(lev1, lev2, lev3));
%aggregation(%str(lev1, lev2));
For the 2nd report above the size of object A is only used once.
Is there any way how I can achieve the same dynamically for a VA 7.5 report?
I'm not very versed with VA so hoping that there is a simple solution to this (like some on-the-fly weight or the like).
To provide some background about the real data:
I'm using exactly the same version of SAS and VA you are.
My approach to doing data preparation for VA is to do it all in Base SAS creating a SAS dataset on disk that then is loaded as is into LASR memory so that the LASR table is an exact copy of the SAS dataset. There are other ways of preparing data to load into VA, but since I already know Base SAS I find that works best for me. I also use SAS code to load the data into VA. The big advantage of this is reloading VA data is just a matter of rerunning your code. We have scheduled SAS jobs that prepare data and load it into VA automatically. I've built a SAS macro for loading VA tables to simplify the process.
Regarding summarizing data in VA, you typically load detail-level data then use VA reports to aggregate. For tabular summary reports, the Crosstab object works well. Think of it like a simplified PROC REPORT that contains GROUP or ACROSS columns. If you stack the GROUP columns then you can drill down levels by clicking on them. You can also define calculated items in VA to go in the report - these are analogous to PROC REPORT calculations.
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 25. 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.