topic Re: Aggregate sums based on condition in SAS Visual Analytics

Aggregate sums based on condition

VMn — Thu, 27 Feb 2025 12:36:44 GMT

Id	date	month	co2
0	12/12/24	12	5
1	5/1/25	1	10
2	13/12/24	12	1
3	5/2/25	2	5
4	27/2/25	2	10
5	14/12/24	12	5

I'm trying to set up some month over month reports. Since my data can encompass a wide range of dates, I've created two calculated items: the_month_two_months_ago and the_month_one_month_ago which represent those respective month digits.

My goal now is to create two additional computed items: 1 for two month's ago co2 aggregate sum and the other for the last month's co2 aggregate sum. But when creating the calculated item for an aggregate sum, I can't figure out how to aggregate based on a conditional using the the_month_two_months_ago and the_month_one_month_ago computed items. Is that even possible? It only seems to ask me ByGroup for ForAll and then the specific metric (co2).

Any guidance would be appreciated!

Re: Aggregate sums based on condition

Madelyn_SAS — Thu, 27 Feb 2025 15:18:28 GMT

Would the AggregateTable function help you?

SAS Visual Analytics: Understanding the AggregateTable Function for Beginners

Re: Aggregate sums based on condition

quickbluefish — Thu, 27 Feb 2025 15:34:57 GMT

How about this - the first data step is just generating some fake data. This allows gaps in months. I am not sure what your ID variable is supposed to be, though:

data have;
date='01Jan2023'd;
format date date9.;
do i=1 to 50;
	date+rand('integer',5,45);
	co2=rand('integer',1,20);
	output;
end;
drop i;
run;

proc sort data=have; by date; run;

proc sql noprint;
select min(intnx('month',date,0)), max(intnx('month',date,0)) 
into :firstmonth trimmed, :lastmonth trimmed from have;
quit;

data _null_;
call symputx('nmonths',intck('month',&firstmonth,&lastmonth)+1);
run;

%put NMONTHS: &nmonths;

data want;
set have end=last;
array T {-1:&nmonths} _temporary_;
T[intck('month',&firstmonth,date)+1]+co2;
if last then do;
	do i=1 to &nmonths;
		yrmonth=put(intnx('month',&firstmonth,i-1),yymmn6.);
		month_m0=T[i];
		month_m1=T[i-1];
		month_m2=T[i-2];
		output;
	end;
end;
keep yrmonth month_:;
run;

proc print data=want; run;

Re: Aggregate sums based on condition

VMn — Fri, 28 Feb 2025 06:30:58 GMT

Thank you! This proved very useful!