Sum variables with the same prefix

[CindyVu]

Hi,

I have monthly data and variable's names are formated with structure of year and month, for example _19991 _19992 ... _199912.

Now I want to sum up 12 months to get annual data. I wrote code below and it works but I wonder whether there is a way to reduce 5 lines to 1 line only.

DATA MB.MB_1995_1999_ANNUAL;
SET MB.MB_1995_1999_MONTHLY;
_1995 = SUM(OF _1995:);
_1996 = SUM(OF _1996:);
_1997 = SUM(OF _1997:);
_1998 = SUM(OF _1998:);
_1999 = SUM(OF _1999:);
RUN;

Can anyone help me? Thanks in advance.

[ChrisNZ]

> I wonder whether there is a way to reduce 5 lines to 1 line only.

No there isn't. You could build a loop, but why?

What you can do to improve you code though is not write everything in uppercase.

[CindyVu]

Hi, I am very new to SAS. Why shouldn't I write everything in uppercase?

For this situation, I have many files with the same format like this. So If I can reduce, it will save more time than modifying every 5 lines when using new data file.

[Reeza]

Not really. You could do an array, but with just 5, you'll end up with the same thing anyways.

If you want a more dynamic approach that will work, I suggest switching your data to a long format in general and then this can easily be accomplished in one PROC.

[CindyVu]

Hi ,

Yes, must 5 is not a big problem. But I have many files with the same format like this, I think it can be some more general codes so I can save time modifying every 5 lines.

I tried to use array but it didn't work. I am new with SAS so still not familiar with using array. Can you write codes for me? My data looks like this

[Reeza]

If you need to deal with this regularly and across more dates I strongly suggest transposing your data, proc transpose, then use PROC MEANS to calculate things more easily.

I can’t work with images. If you need help with code post actual data as text, preferably as a data step. There’s instructions on the forum on how to do so if you do a quick search.

[CindyVu]

Oh, actually I plan to transpose after calculating because I think it's easier to sum up annual data first. 

My data file is attached below. Can you take a look and suggest some code lines? Thank you

[Reeza]

It’s easier after. Try a transpose and proc means.

[Reeza · Accepted solution]

Here's the transpose and sum method:

data long;
set temp;

array _d(*) _20:;

do i=1 to dim(_d);
Year = input(substr(vname(_d(i)), 2, 4), 8.);
Month = input(substr(vname(_d(i)), 6), 8.);
Value = _d(i);
output;
end;

drop _2:;
run;

proc sort data=long;
by 
 Company_Name   CUSIP   Primary_SIC_Code 
 Industry_Sector_Code   Intl_Securities_ID   SEDOL_Code  year month;
 run;

proc means data=long nway noprint;
by 
 Company_Name   CUSIP   Primary_SIC_Code 
 Industry_Sector_Code   Intl_Securities_ID   SEDOL_Code  year;
 var value;
 output out=want sum= ;
 run;

---

@CindyVu wrote:

Oh, actually I plan to transpose after calculating because I think it's easier to sum up annual data first. 

My data file is attached below. Can you take a look and suggest some code lines? Thank you

---

[CindyVu]

Hi, I tried your codes, but after running PROC MEANS, the result does not show sum of every 12 months. Can you check it again?

[Reeza]

I did, it worked fine. Post the actual code you used. Note that MONTH is not in the PROC MEANS, but is in the PROC SORT. SO the sum is happening by YEAR.

[CindyVu]

Oh yes, my bad. I used month in both MEANS and SORT so it didn't work. Thank you so much for your help!