How to sum columns based on date ranges.

bibbnd — Tue, 25 Apr 2023 03:32:59 GMT

I have to get pre and post sums based on install date. The date is in the variable name which i capture using the vname. I am having trouble creating an index on the variables to sum each one based on their install date.

here is my code so far. I am missing something to capture the index of months. I saw a similar post about this but cannot find it anymore.

data want;
set test;
array KWH(*) USAGE_202001 USAGE_202002 USAGE_202003 USAGE_202004
USAGE_202005 USAGE_202006 USAGE_202007 USAGE_202008 USAGE_202009
USAGE_202010 USAGE_202011 USAGE_202012 USAGE_202101 USAGE_202102
USAGE_202103 USAGE_202104 USAGE_202105 USAGE_202106 USAGE_202107
USAGE_202108 USAGE_202109 USAGE_202110 USAGE_202111 USAGE_202112
USAGE_202201 USAGE_202202 USAGE_202203 USAGE_202204 USAGE_202205
USAGE_202206 USAGE_202207 USAGE_202208 USAGE_202209 USAGE_202210
USAGE_202211 USAGE_202012 USAGE_202301 USAGE_202302 USAGE_202303;

array months(*) month1 - month39;

do i = 1 to 39;
months(i) = input(substr(vname(KWH(i)),7,6),6.);
end;

PRE_strt_indx = whichn(PRE_stArt_dt, of months(*));
PRE_end_indx = whichn(PRE_end_dt, of months(*));

* sum the monthly pays in the range;

do i = pre_strt_indx to pre_end_indx;
sum_usage =sum(sum_usage, kwh(i));
end;

run;

Re: How to sum columns based on date ranges.

Kurt_Bremser — Tue, 25 Apr 2023 04:48:21 GMT

Transpose your usage_* variables to a long dataset, and extract the month as a date from _NAME_.

proc transpose
  data=test
  out=long (rename=(col1=usage) where=(usage not in (0,.)))
;
by /* insert your identifying key here */;
var usage_:;
run;

data long_2;
set long;
month = input(scan(_name_,2,'_'),yymmn6.);
format month yymmn6.;
drop _name_;
run;

data want;
merge
  test
  long_2
;
by /* insert key here */;
if first./*key*/
then sum_usage = 0;
if intnx('month',pre_start_dt,0,'b') le month le pre_end_dt then sum_usage + usage;
if last./*key*/;
drop month usage;
run;

topic How to sum columns based on date ranges. in SAS Programming

How to sum columns based on date ranges.

Re: How to sum columns based on date ranges.