🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lapis Lazuli | Level 10

## addition certain columns depends value of a month column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: addition certain columns depends value of a month column

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;``````
8 REPLIES 8
Super User

## Re: addition certain columns depends value of a month column

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;``````
Lapis Lazuli | Level 10

## Re: addition certain columns depends value of a month column

thanks a lot Reeza for quality and reactivity

Lapis Lazuli | Level 10

## Re: addition certain columns depends value of a month column

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 ;``````
Super User

## Re: addition certain columns depends value of a month column

``````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;``````
Lapis Lazuli | Level 10

## Re: addition certain columns depends value of a month column

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)

Lapis Lazuli | Level 10

## Re: addition certain columns depends value of a month column

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

Super User

## Re: addition certain columns depends value of a month column

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;``````
Lapis Lazuli | Level 10

## Re: addition certain columns depends value of a month column

Thanks a lot Ksharp.

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

have a nice day.

Discussion stats
• 8 replies
• 1052 views
• 3 likes
• 3 in conversation