Creating rows with missing values

Reply
Occasional Contributor
Posts: 17

Creating rows with missing values

[ Edited ]

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

 

Super User
Super User
Posts: 7,413

Re: Creating rows with missing values

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;
Occasional Contributor
Posts: 17

Re: Creating rows with missing values

[ Edited ]

@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

Super User
Posts: 10,532

Re: Creating rows with missing values

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.

 

Super User
Super User
Posts: 7,413

Re: Creating rows with missing values

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;
Ask a Question
Discussion stats
  • 4 replies
  • 142 views
  • 0 likes
  • 3 in conversation