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

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
Reeza
Super User

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

8 REPLIES 8
Reeza
Super User

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

thanks a lot Reeza for quality and reactivity

Nasser_DRMCP
Lapis Lazuli | Level 10

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
Super User
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
Lapis Lazuli | Level 10

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

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

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

Thanks a lot Ksharp.

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

have a nice day.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 8 replies
  • 1040 views
  • 3 likes
  • 3 in conversation