Need help in creating new table

[bd_user_10]

Hi Everyone,

This is what I have

Date                    isin                    dummy               price      r_stock           oib     month

2000-03-28    FI0001001234            1                     70           0.060            0.67         3

2000-03-29    FI0001001234            1                     71           0.102             1             3

2000-03-30    FI0001001234            1                     69           -0.052           -1             3

2000-03-31    FI0001001234            1                     68           -0.050            0.33        3

2000-04-01    FI0001001234            1                     69            0.070             1            4

I want this

Date                       isin                         dummy     price      r_stock           oib          month

2000-03-31     FI0001001234                   1            68           0.015             1                3

Here,

Date is the last day of the month

Isin is the isin for the company                              (I have over 200 different isin)

Dummy is the dummy for the given isin                 (it could be zero for other isin)

Price is the price on the last day of the month              

r_stock is the average r_stock of the month

oib is the total sum of the month

month is the relevant month

I have some missing values in the dataset. All missing values are in the "." form.

Can someone help? Thanks.

[PaigeMiller · Accepted solution]

/* UNTESTED CODE */


data have2;
    set have;
    if date = intnx('month',date,0,'e') then last_day=1;
    else last_day=0;
    format date mmdd4.;
run;
proc summary data=have2;
    class date isin;
    id dummy month;
    var r_stock oib;
    var price/weight=last_day;
    output out=want sum(oib)=sum_oib mean(r_stock)=mean_r_stock
         sum(price)=price_on_last_day_of_month;
run;

[andreas_lds]

Can be solved with a data-step and retained variables.

data want;
  set have;
  by isin date;
  
  length lastPrice avgStock sumStock sumOIB numObs 8;
  retain lastPrice sumStock sumOIB numObs;

  if first.date then do;
    /* set variables in retain to 0 */
  end;

  numObs = numObs +1;
  sumStock = sum(sumStock, r_stock);
  ...

  if last.date then do;
    avgStock = sumStock / numObs;
    output;
  end;

  keep Date isin dummy lastPrice sumStock sumOIB month;
  rename lastPrice = price ....;
run;

Post test-data as data-step using datalines if you want tested code.

[PaigeMiller]

---

@andreas_lds wrote:

Can be solved with a data-step and retained variables.

data want;
  set have;
  by isin date;
  
  length lastPrice avgStock sumStock sumOIB numObs 8;
  retain lastPrice sumStock sumOIB numObs;

  if first.date then do;
    /* set variables in retain to 0 */
  end;

  numObs = numObs +1;
  sumStock = sum(sumStock, r_stock);
  ...

  if last.date then do;
    avgStock = sumStock / numObs;
    output;
  end;

  keep Date isin dummy lastPrice sumStock sumOIB month;
  rename lastPrice = price ....;
run;

---

It does not appear to me that this produces results by month, it appears to me that this gives results over the entire data set.

[andreas_lds]

@PaigeMiller: my fault .... you are right ... still no usable test-data, so i won't suggest anything 😐