Hi Guys, what code can i use to convert quarterly data to Annual data; Here is a sample of the data. You will notice some quarters in some years are missing. But ideally i want to have annual figures summed up from quarterly figures per year per "Accper". I want to have totals for TOR and TOE in two new columns computed from adding up the different figures from the different quarters.
Stkcd | Accper | TOR | TOE | year | month | day | Quarter |
1 | 31/12/1991 | 334690000 | 188230000 | 1991 | 12 | 31 | 4 |
1 | 31/12/1992 | 475510800 | 254612800 | 1992 | 12 | 31 | 4 |
1 | 30/06/1993 | 266495500 | 0 | 1993 | 6 | 30 | 2 |
1 | 31/12/1993 | 407870596.5 | 114621987.4 | 1993 | 12 | 31 | 4 |
1 | 30/06/1994 | 297087559.4 | 99154582.93 | 1994 | 6 | 30 | 2 |
1 | 31/12/1994 | 662338768.2 | 323169773.6 | 1994 | 12 | 31 | 4 |
1 | 30/06/1995 | 415720836.9 | 181933045.4 | 1995 | 6 | 30 | 2 |
1 | 31/12/1995 | 944990411.4 | 480495603.7 | 1995 | 12 | 31 | 4 |
1 | 30/06/1996 | 652159903.4 | 274865660.9 | 1996 | 6 | 30 | 2 |
1 | 31/12/1996 | 1261061632 | 495232702.5 | 1996 | 12 | 31 | 4 |
1 | 30/06/1997 | 650518641 | 300511899.8 | 1997 | 6 | 30 | 2 |
1 | 31/12/1997 | 1348895058 | 733785961.5 | 1997 | 12 | 31 | 4 |
1 | 30/06/1998 | 694283519.3 | 330525163.8 | 1998 | 6 | 30 | 2 |
1 | 31/12/1998 | 1758111658 | 1117254216 | 1998 | 12 | 31 | 4 |
1 | 30/06/1999 | 498367080 | 328570297.8 | 1999 | 6 | 30 | 2 |
1 | 31/12/1999 | 1471220690 | 1119905523 | 1999 | 12 | 31 | 4 |
1 | 30/06/2000 | 563942659 | 524513922 | 2000 | 6 | 30 | 2 |
1 | 31/12/2000 | 1431286264 | 1259262034 | 2000 | 12 | 31 | 4 |
1 | 30/06/2001 | 836514154 | 768024073 | 2001 | 6 | 30 | 2 |
1 | 31/12/2001 | 2118441363 | 2035715964 | 2001 | 12 | 31 | 4 |
1 | 31/03/2002 | 595998313 | 489611047 | 2002 | 3 | 31 | 1 |
1 | 30/06/2002 | 1315435885 | 1289009315 | 2002 | 6 | 30 | 2 |
1 | 30/09/2002 | 2120786642 | 2035086853 | 2002 | 9 | 30 | 3 |
1 | 31/12/2002 | 3077098847 | 3120449041 | 2002 | 12 | 31 | 4 |
1 | 31/03/2003 | 649968516 | 650028133 | 2003 | 3 | 31 | 1 |
1 | 30/06/2003 | 1428220006 | 1432624888 | 2003 | 6 | 30 | 2 |
1 | 30/09/2003 | 2734822115 | 2839527893 | 2003 | 9 | 30 | 3 |
1 | 31/12/2003 | 3128836264 | 3482265794 | 2003 | 12 | 31 | 4 |
may something like this might work.
proc sql;
select a.*, sum(TOR) as sum_tor, sum(TOE) as sum_toe from have
group by year, quarter;
quit;
proc sql;
create table want as
select year, sum(TOR) as TOR, sum(TOE) as TOE
from have
group by year;
quit;
or
proc summary data=have;
class year;
var TOR TOE;
output
out=want (drop=_type_ _freq_)
sum(TOR)=TOR
sum(TOE)=TOE
;
run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.