DATA Step, Macro, Functions and more

Sum with conditions

Reply
Occasional Contributor
Posts: 8

Sum with conditions

Fundyear

month

SecurityIDMarket value of securitiesNewvar
119901a10003000
119901b20003000
119903a12002300
119903b11002300
119902a500
1199011a400
219903a1300
219903b300

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

Super User
Posts: 17,824

Re: Sum with conditions

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.

Occasional Contributor
Posts: 8

Re: Sum with conditions

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!

Occasional Contributor
Posts: 8

Re: Sum with conditions

Checked it & it worked!

Ask a Question
Discussion stats
  • 3 replies
  • 225 views
  • 3 likes
  • 2 in conversation