topic Re: Need help in creating new table in SAS Programming

Need help in creating new table

bd_user_10 — Tue, 11 Sep 2018 12:06:27 GMT

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.

Re: Need help in creating new table

PaigeMiller — Tue, 11 Sep 2018 13:33:36 GMT

/* 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;

Re: Need help in creating new table

andreas_lds — Tue, 11 Sep 2018 12:32:28 GMT

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.

Re: Need help in creating new table

PaigeMiller — Tue, 11 Sep 2018 13:47:31 GMT

@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.

Re: Need help in creating new table

andreas_lds — Tue, 11 Sep 2018 14:09:21 GMT

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