DATA Step, Macro, Functions and more

addition certain columns depends value of a month column

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

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

 


Accepted Solutions
Solution
‎10-17-2017 12:15 PM
Super User
Posts: 23,266

Re: addition certain columns depends value of a month column

Posted in reply to Nasser_alfea

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;

View solution in original post


All Replies
Solution
‎10-17-2017 12:15 PM
Super User
Posts: 23,266

Re: addition certain columns depends value of a month column

Posted in reply to Nasser_alfea

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;
Frequent Contributor
Posts: 117

Re: addition certain columns depends value of a month column

thanks a lot Reeza for quality and reactivity

Frequent Contributor
Posts: 117

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
Posts: 10,689

Re: addition certain columns depends value of a month column

Posted in reply to Nasser_alfea
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;
Frequent Contributor
Posts: 117

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)

Frequent Contributor
Posts: 117

Re: addition certain columns depends value of a month column

Posted in reply to Nasser_alfea

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
Posts: 10,689

Re: addition certain columns depends value of a month column

Posted in reply to Nasser_alfea

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;
Frequent Contributor
Posts: 117

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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