addition certain columns depends value of a month column

[Nasser_DRMCP]

Hello,

I have this dataset

data table ;

input year_month NB_201701 NB_201702 NB_201703 ;

datalines;

201701 101 102 103

201702 201 202 203

201703 301 302 303

;

run ;

And I would like to get a new aggregated column that holds the addition of each column which have a month (in the column name) less than the month in the "year_month" column.

1rst line==> because it is jan then the result shoul be NB_201701 only

2nd ==>NB_201701 + NB_201702

3==> NB_201701 + NB_201702 + NB_201703

Thanks a lot in davance for your help

Nasser

[Reeza · Accepted solution]

Here's one method. Get the month from the date variable and then use that to determine how many items you need to add.

data have ;
informat year_month yymmn6.;
format year_month date9.;
input year_month NB_201701 NB_201702 NB_201703 ;
datalines;
201701 101 102 103
201702 201 202 203
201703 301 302 303
;
run ;

data want;
set have;
array nb(3) nb_2017:;

month = month(year_month);
do i=1 to month;
want_total = sum(want_total, nb(i));
end;
run;

[Nasser_DRMCP]

thanks a lot Reeza for quality and reactivity

[Nasser_DRMCP]

hello Reeza

please could I ask an added question. this is the new dataset "have" that contains 2 years 2016 and 2017.

I would like to sum only column NB_2016_ whenever the row concerns 2016

and sum NB_2017_ whenever the row concerns 2017.

thanks a lot in advance !

Nasser

data have ;

informat year 4. ;

informat year_month yymmn6.;

format year_month date9.;

input year year_month NB_201601 NB_201602 NB_201603 NB_201701 NB_201702 NB_201703 ;

datalines;

2016 201601 1611 1612 1613 1711 1712 1713

2016 201602 1621 1622 1623 1721 1722 1723

2016 201603 1631 1632 1633 1731 1732 1733

2017 201701 1611 1612 1613 1711 1712 1713

2017 201702 1621 1622 1623 1721 1722 1723

2017 201703 1631 1632 1633 1731 1732 1733

;

run ;

[Ksharp]

data have ; 
informat year 4. ; 
informat year_month yymmn6.;
format year_month date9.;
input year year_month NB_201601 NB_201602 NB_201603 NB_201701 NB_201702 NB_201703 ;
datalines; 
2016 201601 1611 1612 1613 1711 1712 1713 
2016 201602 1621 1622 1623 1721 1722 1723 
2016 201603 1631 1632 1633 1731 1732 1733 
2017 201701 1611 1612 1613 1711 1712 1713 
2017 201702 1621 1622 1623 1721 1722 1723 
2017 201703 1631 1632 1633 1731 1732 1733 
; 
run ;
data want;
 set have;
 array x{*} NB_:;
 sum=0;
 do i=1 to dim(x);
   if upcase(vname(x{i})) =: cats('NB_',year) then sum+x{i};
 end;
drop i;
run;

[Nasser_DRMCP]

thank you very muck ksharp.

your respons would help me even if it is not exactly what I want.

in your result, the amount of the rows 1, 2, 3 (year  2016) is the same . But what I want is

row 1 : 201601 only

row 2 : 201601 + 201602

row 3 : 201601 + 201602 + 201603

row4 : 201701 only

row5 : 201701 + 201702

row6 : 201701 + 201702 + 201703

could you explain this condition ? I understand its idea (purpose) but upcase(vname(x{i})) returns something like NB_201601 but cats('NB_',year) returns something like NB_2016

upcase(vname(x{i})) =: cats('NB_',year)

[Nasser_DRMCP]

Now I get exactly what I wanted. I just remplaced

upcase(vname(x{i})) =: cats('NB_',year)

by  upcase(vname(x{i})) >=: cats('NB_',year01) 

and upcase(vname(x{i})) <=: cats'NB_',year)

the new column year01 is 201601 or 201701

thanks a lot for your help

[Ksharp]

Ou. That would be more complicated.

data have ; 
input year year_month NB_201601 NB_201602 NB_201603 NB_201701 NB_201702 NB_201703 ;
datalines; 
2016 201601 1611 1612 1613 1711 1712 1713 
2016 201602 1621 1622 1623 1721 1722 1723 
2016 201603 1631 1632 1633 1731 1732 1733 
2017 201701 1611 1612 1613 1711 1712 1713 
2017 201702 1621 1622 1623 1721 1722 1723 
2017 201703 1631 1632 1633 1731 1732 1733 
; 
run ;
data want;
 set have;
 array x{*} NB_:;
 sum=0;
 do i=1 to dim(x);
   if upcase(vname(x{i})) =: cats('NB_',year) and
     input(scan(vname(x{i}),-1,'_'),best.) <= year_month
  then sum+x{i};
 end;
drop i;
run;

[Nasser_DRMCP]

Thanks a lot Ksharp.

I succeeded to get the intended result thank to you suggestion.

have a nice day.