topic Creating rows with missing values in SAS Programming

Creating rows with missing values

Akarsh91 — Wed, 07 Jun 2017 11:54:34 GMT

Dear experts,

I have the following data set:

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month newmonth year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,07,1967
1,1967-11-28,1.026,,11,08,1967
1,1967-12-28,1.027,,12,09,1967
1,1968-01-28,1.01,,1,10,1968
1,1968-02-28,1.04,,2,11,1968
1,1968-03-28,1.001,,3,12,1968
1,1968-04-28,1.005,,4,01,1968
1,1968-05-28,1.02,,5,02,1968
1,1968-06-28,1.02,,6,03,1968
1,1968-07-28,1.06,,7,04,1968
1,1968-08-28,1.06,,8,05,1968
1,1968-09-28,1.0014,1.0014,9,06,1968;
run;

ID Date Returns Delisting return month newmonth year

1 1967-10-28 1.025 10 07 1967

1 1967-11-28 1.026 11 08 1967

1 1967-12-28 1.027 12 09 1967

1 1968-01-28 1.01 1 10 1968

1 1968-02-28 1.04 2 11 1968

1 1968-03-28 1.001 3 12 1968

1 1968 -04-28 1.005 . 4 1 1968

1 1968-05-28 1.02 5 2 1968

1 1968-06-28 1.02 6 3 1968

1 1968-07-28 1.06 7 4 1968

1 1968-08-28 1.06 8 5 1968

1 1968-09-28 1.0014 1.0014 9 6 1968

Id is only 1 here because I only have 1 stock in a portfolio. I am replicating a paper. I buy a stock in april (04 1968). so this becomes the first month for me (the month I bought the stock in(newmonth in the table above). For the purpose of the paper, if the stock gets delisted then the return is replaced with the size decile return until the end of the year, relative to formation. So i bought the stock in april 1968 i only held it for 6 months. So for me it got delisted after 6 months I bought it. So for the months 7 - 12 (actual 9-1968, to 4-1969) the return is to be replaced with the size delcille return. So the output I would like to obtain is as follows:

ID Date Returns Delisting return month newmonth year

1 1967-10-28 1.025 10 07 1967

1 1967-11-28 1.026 11 08 1967

1 1967-12-28 1.027 12 09 1967

1 1968-01-28 1.01 1 10 1968

1 1968-02-28 1.04 2 11 1968

1 1968-03-28 1.001 3 12 1968

1 1968 -04-28 1.005 . 4 1 1968

1 1968-05-28 1.02 5 2 1968

1 1968-06-28 1.02 6 3 1968

1 1968-07-28 1.06 7 4 1968

1 1968-08-28 1.06 8 5 1968

1 1968-09-28 1.0014 1.0014 9 6 1968

1 1968-10-28 . . 10 7 1968

1 1968-11-28 . . 11 8 1968

1 1968-12-28 . 12 9 1968

1 1969-01-28 . . 1 10 1968

1 1969-02-28 . . 2 11 1968

1 1969-03-28 . . 3 12 1968

1 1969-04-28 . . 4 01 1968

I would like to create empty rows according to the new month. If the stock gets delisted in the new month = 06 then i want to insert empty rows after that with missing values for return and delisting return as above until the new month = 12.

I have tried using this code code

data want; 
set have;
by ID;
output;
if last.ID and newmonth < 12 then do;
call missing(ret, dlret);
do while (newmonth <12);
output;
end
end;
run;

Sas however stops working whenever I used it. Is there any other code that I could use to accomplish the above task? Or could anyone correct this code?

All help is appreciated. Thankyou in advance.

Regards

Akarsh91

Re: Creating rows with missing values

RW9 — Wed, 07 Jun 2017 12:44:36 GMT

Something like:

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month newmonth year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,07,1967
1,1967-11-28,1.026,,11,08,1967
1,1967-12-28,1.027,,12,09,1967
1,1968-01-28,1.01,,1,10,1968
1,1968-02-28,1.04,,2,11,1968
1,1968-03-28,1.001,,3,12,1968
1,1968-04-28,1.005,,4,01,1968
1,1968-05-28,1.02,,5,02,1968
1,1968-06-28,1.02,,6,03,1968
1,1968-07-28,1.06,,7,04,1968
1,1968-08-28,1.06,,8,05,1968
1,1968-09-28,1.0014,1.0014,9,06,1968
;
run; 

data want (drop=i);
  set have;
  by id;
  if last.id and month(date) ne 12 then do;
    do i=0 to 11-month(date);
      date=intnx('month',date,i);
      if i > 0 then returns=.;
      month=month(date);
      newmonth=.;
      year=year(date);
      output;
    end;
  end;
  else output;
run;

Re: Creating rows with missing values

Akarsh91 — Wed, 07 Jun 2017 13:07:46 GMT

@RW9

Thank you for your reply. I would like to however have the output I mentioned above:

1968 -04-28 1.005 . 4 1 1968

1 1968-05-28 1.02 5 2 1968

1 1968-06-28 1.02 6 3 1968

1 1968-07-28 1.06 7 4 1968

1 1968-08-28 1.06 8 5 1968

1 1968-09-28 1.0014 1.0014 9 6 1968

1 1968-10-28 . . 10 7 1968

1 1968-11-28 . . 11 8 1968

1 1968-12-28 . 12 9 1968

1 1969-01-28 . . 1 10 1968

1 1969-02-28 . . 2 11 1968

1 1969-03-28 . . 3 12 1968

My stock gets delisted on the actual date of 9-1968. I need to replace the return with an average value ( in this case i want it to be missing, hence the rows with missing values), until the march next next year. For example if stock gets delisted on 09 1968 then i want rows with missing values until 03 1969. Since I am buying stocks on april it will be month 1 for me after i bought the stock. So I created the column new month.

With the code you sent I only get the missing values until the month of 12 (actual month). I want to obtain emplty rows according to the new month. If new month is =6 then I would like to obtain 6 missing rows underneath. Is it possible to adjust the code you sent such that the missing values are created for the formation of april to april acccording to the new monthas above? Could you please help with adjusting it?

Akarsh 91.

PS: I am using sas 9.4

Re: Creating rows with missing values

ballardw — Wed, 07 Jun 2017 14:27:35 GMT

Perhaps you intend

1 1969-01-28 . . 1 10 1969

1 1969-02-28 . . 2 11 1969

1 1969-03-28 . . 3 12 1969

For the last 3 lines?

And this rule " 09 1968 then i want rows with missing values until 03 1969" does NOT tell what to do if another stock is delisted 06 1969.

Why the magic end date ot 03 1969? What is determining that is the last date? Your original input example data does not show anything that would trigger 1968-04 to be special for anything in theis process. Your narrative mentions "Since I am buying stocks on april it will be month 1 for me after i bought the stock" but there is nothing in the start data to indicate that.

Re: Creating rows with missing values

RW9 — Wed, 07 Jun 2017 14:35:17 GMT

Updated:

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month newmonth year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,07,1967
1,1967-11-28,1.026,,11,08,1967
1,1967-12-28,1.027,,12,09,1967
1,1968-01-28,1.01,,1,10,1968
1,1968-02-28,1.04,,2,11,1968
1,1968-03-28,1.001,,3,12,1968
1,1968-04-28,1.005,,4,01,1968
1,1968-05-28,1.02,,5,02,1968
1,1968-06-28,1.02,,6,03,1968
1,1968-07-28,1.06,,7,04,1968
1,1968-08-28,1.06,,8,05,1968
1,1968-09-28,1.0014,1.0014,9,06,1968
;
run; 

data want (drop=i fst_date fst_newmonth);
  set have;
  by id;
  retain fst_date fst_newmonth;
  if last.id and month(date) ne 12 then do;
    fst_date=date;
    fst_newmonth=newmonth;
    do i=0 to 12-newmonth;
      date=intnx('month',fst_date,i);
      if i > 0 then returns=.;
      month=month(date);
      year=year(date);
      delisting_return=.;
      newmonth=fst_newmonth+i;
      output;
    end;
  end;
  else output;
run;