topic Sum with conditions in SAS Programming

Sum with conditions

DaanUtrecht — Mon, 04 Mar 2013 16:25:28 GMT

Fund	year	month	SecurityID	Market value of securities	Newvar
1	1990	1	a	1000	3000
1	1990	1	b	2000	3000
1	1990	3	a	1200	2300
1	1990	3	b	1100	2300
1	1990	2	a	500
1	1990	11	a	400
2	1990	3	a	1300
2	1990	3	b	300

I am a beginning SAS user. The upper table is an example of my dataset. I want to create a new variable, up till now I only have experience with creating new variables without conditions. However, now I need to use conditions and I do not know how to get this into SAS.

The new variable should be the sum of the marketvalue of all securities of a specific fund at a specific month of a specific year. To gain understanding I did this for the first 4 rows already. 3000 is the total value in January 1990 because the fund holds 2 securities with respective values of 1000 and 2000 as can be seen in the first two rows.

I hope it is understandable that I need a condition that specifies that that a summation should be done of marketvalue for specific values "year" and "month". So for example all 1990/1, then 1990/2 etc. etc.

How can I approach this problem? I have the feeling I should use some sort of loop is that correct?

Sincerely,

Daan

Message was edited by: Daan van Alem

Re: Sum with conditions

Reeza — Mon, 04 Mar 2013 16:38:47 GMT

You have a few options, I'll suggest option 2.

1. Proc means to summarize the data and merge it in.

2. Proc SQL with a group by clause

proc sql;

create table want as

select *, sum(market_value) as new_variable

from have

group by fund, year, month;

quit;

3. DOW loop (you can google that one).

It's difficult for beginners so I don't recommend it if you're just learning SAS.

Re: Sum with conditions

DaanUtrecht — Mon, 04 Mar 2013 16:44:02 GMT

Yes, the problem was that browsing for a solution only made me stumble upon stuff that was above my level. But your 2nd option seems very doable. Didn;t realise there is also a group by function! Will google the sql function and will try it! Thanks for your advice!

Re: Sum with conditions

DaanUtrecht — Mon, 04 Mar 2013 17:15:18 GMT

Checked it & it worked!