BookmarkSubscribeRSS Feed
Sandeep_Gupta
Calcite | Level 5
Hi Everyone, I am having trouble calculating weighted average return from the stock. I want required_price to be returned wherever FLAG is 'COVER' as shown below: ACCOUNT_NO STOCK_CODE ORDER_REF TRADE_DATE FLAG QTY PRICE required_price 101 NIFTY 7622 13May2014 FRESH 50 7137.65 101 NIFTY 14474 22May2014 FRESH 50 7350.75 101 NIFTY 18359 29May2014 COVER -50 7234.3 (50*7137.65+50*7350.75)/(50+50) 101 NIFTY 18026 05Jun2014 FRESH 50 7479.75 101 NIFTY 464 06Jun2014 FRESH 50 7528.75 101 NIFTY 20830 18Jun2014 COVER -100 7554.475 (50*7479.75+50*7528.75)/(50+50) 101 NIFTY 12253 19Jun2014 COVER -50 7530.1 (50*7479.75+50*7528.75+50*7137.65+50*7350.75)/(50+50+50+50) 101 NIFTY 16690 22Jul2014 FRESH 100 7768.4 101 NIFTY 7164 23Jul2014 FRESH 100 7800 101 NIFTY 16070 24Jul2014 FRESH 50 7834.1 101 NIFTY 11726 30Jul2014 COVER -250 7722.9 (100*7768.4+100*7800+50*7834.1) Regards Sandeep Gupta
5 REPLIES 5
Sandeep_Gupta
Calcite | Level 5
ACCOUNT_NOSTOCK_CODEORDER_REFTRADE_DATEFLAGQTYPRICErequired_price
101NIFTY762213May2014FRESH507137.65 
101NIFTY1447422May2014FRESH507350.75 
101NIFTY1835929May2014COVER-507234.3(50*7137.65+50*7350.75)/(50+50)
101NIFTY1802605Jun2014FRESH507479.75 
101NIFTY46406Jun2014FRESH507528.75 
101NIFTY2083018Jun2014COVER-1007554.475(50*7479.75+50*7528.75)/(50+50)
101NIFTY1225319Jun2014COVER-507530.1(50*7479.75+50*7528.75+50*7137.65+50*7350.75)/(50+50=50+50)
101NIFTY1669022Jul2014FRESH1007768.4 
101NIFTY716423Jul2014FRESH1007800 
101NIFTY1607024Jul2014FRESH507834.1 
101NIFTY1172630Jul2014COVER-2507722.9(100*7768.4+100*7800+50*7834.1)
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, what is the question.  You seem to have it all there.  

data want;

  set have;

  retain inter;

  inter=qty*price;

  if flag="COVER" then do;

    required_price=inter;

    inter=.;

  end;

run;

 

This will get you some of the way, however I don't see why the Cover in the 7th row should have all that formula, maybe as it follows another cover row, if so then just update the logic above:

data want;

  set have;

  retain inter;

  if flag="COVER" and lag(flag) ne "COVER then inter=.;

  inter=qty*price;

  if flag="COVER" then do;

    required_price=inter;

  end;

run;

ballardw
Super User

It appears that you have two heirarchies of data. I would consider adding a variable that defines which group is being looked

Steelers_In_DC
Barite | Level 11

It looks like you are leaving out some important information.  Why does the first and second cover have two fresh calculations a piece but the third has 4 and the 4th has 3?

Sandeep_Gupta
Calcite | Level 5

Hi,

 

I want  to put weighted average price in front of cover. On 29may the required price will be weighted average of price on 13th and 22nd may2015.

 

Regards

Sandeep

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1369 views
  • 0 likes
  • 4 in conversation