topic Re: Sum variables with the same prefix in SAS Programming

Sum variables with the same prefix

CindyVu — Wed, 23 Jan 2019 02:38:29 GMT

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.

Re: Sum variables with the same prefix

ChrisNZ — Wed, 23 Jan 2019 03:23:11 GMT

> 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.

Re: Sum variables with the same prefix

Reeza — Wed, 23 Jan 2019 03:23:37 GMT

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.

Re: Sum variables with the same prefix

CindyVu — Wed, 23 Jan 2019 04:09:01 GMT

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.

Re: Sum variables with the same prefix

CindyVu — Wed, 23 Jan 2019 04:11:14 GMT

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

Re: Sum variables with the same prefix

Reeza — Wed, 23 Jan 2019 04:18:18 GMT

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.

Re: Sum variables with the same prefix

CindyVu — Wed, 23 Jan 2019 04:52:11 GMT

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

Re: Sum variables with the same prefix

Reeza — Wed, 23 Jan 2019 04:56:00 GMT

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

Re: Sum variables with the same prefix

Reeza — Wed, 23 Jan 2019 15:57:04 GMT

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

Re: Sum variables with the same prefix

CindyVu — Wed, 23 Jan 2019 18:25:37 GMT

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?

Re: Sum variables with the same prefix

Reeza — Wed, 23 Jan 2019 18:26:44 GMT

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.

Re: Sum variables with the same prefix

CindyVu — Wed, 23 Jan 2019 18:29:26 GMT

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