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
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.
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!
Checked it & it worked!
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.