BookmarkSubscribeRSS Feed
DaanUtrecht
Calcite | Level 5
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

3 REPLIES 3
Reeza
Super User

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.

DaanUtrecht
Calcite | Level 5

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!

DaanUtrecht
Calcite | Level 5

Checked it & it worked!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 849 views
  • 3 likes
  • 2 in conversation