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.
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
> 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.
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.
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.