BookmarkSubscribeRSS Feed
bibbnd
Fluorite | Level 6

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;

1 REPLY 1
Kurt_Bremser
Super User

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;

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 408 views
  • 2 likes
  • 2 in conversation