topic Re: converting months to quarters in New SAS User

converting months to quarters

Raj00007 — Fri, 14 Aug 2020 06:08:23 GMT

hi,

i have 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

and i want into convert into like this.

Obs Year Quarter1 Quarter2 Quarter3 Quarter4
1 2009 $307187244 $473194898 $546138514 $343835099
2 2010 $458460158 $792776008 $578998287 $414642789
3 2011 $200158418 $818075078 $309234158 $501240036

how can i do this? Thanks .

Re: converting months to quarters

Shmuel — Fri, 14 Aug 2020 06:45:29 GMT

data want;
 set have;
     array mx Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ;
	 array qt Quarter1 Quarter2 Quarter3 Quarter4 ;
	 do i=1 to 12 by 3;
	    j= int((i-1)/3 +1);
		qt(j) = sum(of mx(i), mx(i+1),mx(i+2));
	 end;
     keep year Quart:;
     format Quarter1-Quarter4 dollar12.;
run;

Re: converting months to quarters

andreas_lds — Fri, 14 Aug 2020 06:40:50 GMT

Can you fix the form in which you get the data? The first table you have posted looks like a report, not like a dataset. If you can't fix it, try something like (untested code):

data want;
  set have;
  Quarter1 = sum(Jan, Feb, Mar);
  Quarter2 = ...;
  drop Jan--Dec;
  format Quarter: dollar.;
run;

Re: converting months to quarters

Kurt_Bremser — Fri, 14 Aug 2020 07:27:00 GMT

Transpose your input dataset to a usable dataset structure, and everything else is a piece of cake:

data input;
input Obs Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;
datalines;
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
;

proc transpose data=input out=long;
by obs year;
var jan--dec;
run;

data have;
set long;
period = input(cats(_name_,year),monyy7.);
quarter = intnx('quarter',period,0,'b');
format period yymmd7. quarter yyq7. col1 dollar20.;
drop _name_ year;
rename col1=amount;
run;

proc summary data=have nway;
class obs quarter;
var amount;
output out=want (drop=_freq_ _type_) sum(amount)=;
run;