topic Re: Aggregate monthly data to quarters using arrays in SAS Programming

Aggregate monthly data to quarters using arrays

Raj00007 — Tue, 24 Nov 2020 02:48:19 GMT

Hi i have a data like this

Obs Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 
1 2009 192284420 86376721 28526103 260386468 109975326 102833104 196728648 236996122 112413744 125401565 82391029 136042505 
2 2010 108645734 147656369 202158055 41160707 300627381 450987920 208694865 83456868 286846554 275721406 34982991 103938392 
3 2011 85730444 74328989 40098985 312654811 318149340 187270927 123394421 34273985 151565752 141528519 178043261 181668256

how can i convert it to like this using arrays

Year qtr1 qtr2 qtr3 qtr4
2009 307187244 473194898 546138514 343835099
2010 765647402 1265970906 1125136801 758477888
2011 965805820 2084045984 1434370959 1259717924

Re: array

PaigeMiller — Tue, 24 Nov 2020 01:07:56 GMT

Putting calendar information in variable names is an extremely poor choice, making the programming much more complicated. The problem becomes much simpler if you have a long data set, where month or other calendar information is in a variable rather than in the variable name. Example:

year     month value
2009         1 192284420
2009         2 863767218

and so on. Now, to get quarters, month 1 2 and 3 are the first quarter, 4 5 6 are the 2nd quarter ... and then PROC SUMMARY gets the sum by year and quarter with very little programming.

Re: array

ChrisNZ — Tue, 24 Nov 2020 02:35:00 GMT

Your table's layout is poor, and adapted for reporting, not for data storage.

That's why you are struggling. Writing code to create another equally bad layout can only cause you more problems.

A database is not Excel, and you should make life easy for yourself by storing the date as a column. You can then report however you like.

Re: Aggregate monthly data to quarters using arrays

Reeza — Tue, 24 Nov 2020 02:57:52 GMT

In future posts, it would help if you used more descriptive titles and included your logs when you post a question that has code and errors. It's also generally considered best practice to show what you've tried on a question. It's also good practice to mark questions as solved when they're answered.

There's a few ways to do this with arrays but the simplest is shown below. You won't get much advantage (if any) by using arrays in this particular use case. You should be able to extend my logic to other quarters.

You would actually get better efficiency (coding and speed) from transposing the data to a long format and using proc tabulate + a format or some other summary procedures.

array _months(12) Jan -- Dec;
qtr1 = sum(_months(1), _months(2), _months(3));

@Raj00007 wrote:

Hi i have a data like this

Obs Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 
1 2009 192284420 86376721 28526103 260386468 109975326 102833104 196728648 236996122 112413744 125401565 82391029 136042505 
2 2010 108645734 147656369 202158055 41160707 300627381 450987920 208694865 83456868 286846554 275721406 34982991 103938392 
3 2011 85730444 74328989 40098985 312654811 318149340 187270927 123394421 34273985 151565752 141528519 178043261 181668256

how can i convert it to like this using arrays

Year qtr1 qtr2 qtr3 qtr4
2009 307187244 473194898 546138514 343835099
2010 765647402 1265970906 1125136801 758477888
2011 965805820 2084045984 1434370959 1259717924

Re: Aggregate monthly data to quarters using arrays

Kurt_Bremser — Tue, 24 Nov 2020 05:10:12 GMT

Maxims 19 & 33.

data have_bad;
input Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;
datalines;
2009 192284420 86376721 28526103 260386468 109975326 102833104 196728648 236996122 112413744 125401565 82391029 136042505 
2010 108645734 147656369 202158055 41160707 300627381 450987920 208694865 83456868 286846554 275721406 34982991 103938392 
2011 85730444 74328989 40098985 312654811 318149340 187270927 123394421 34273985 151565752 141528519 178043261 181668256
;

proc transpose
  data=have
  out=long
;
by year;
var Jan--Dec;
run;

data have_good;
set long;
date = input(cats(_name_,year),monyy7.);
format date yymmdd10.;
drop _name_ year;
rename col1=value;
run;

proc summary data=have_good;
by date;
var value;
format date yyq6.;
output out=want (drop=_:) sum()=;
run;

With an intelligent layout, using SAS procedures makes the coding simple.

Re: Aggregate monthly data to quarters using arrays

mkeintz — Tue, 24 Nov 2020 05:16:02 GMT

This is place where using a 2-dimensional array simplifies the task:

data want (drop=_:);
  set have;
  array mth {4,3} JAN--DEC;
  array qtr{4} qtr1-qtr4;
  do _q=1 to 4;
    qtr{_q}=mth{_q,1}+mth{_q,2}+mth{_q,3};
  end;
run;

The array mth has 4 rows and 3 columns, conveniently making each row correspond to the months in a single quarter.

Re: Aggregate monthly data to quarters using arrays

Reeza — Tue, 24 Nov 2020 16:39:47 GMT

In this particular example it's the exact same number of lines of code. I think the array would add unnecessary overhead in such a simple calculation.