How to Create extra rows with missing values?

[Akarsh91]

Dear Experts, 

I have the following information: 

ID    Date                Returns      Delisting return        month         year

1   1967-10-29     0.025                                            10            1967

1  1967-11-29      0.026                                            11            1967

1    1967-12-29     0.027                                            12            1967

1    1968-01-29                                   0.01                  1             1968

The stock (ID=1) gets delisted on 1968-01-29. From 1968-02-01 onwards i want to assign a return of 0.025 until the end of the year that is 1968-12-29. i am thinking of creating missing values and then replacing those with the return, so the output I would like to obtain would be as follows:

ID Date                Returns      Delisting return        month         year

1   1967-10-29     0.025                                            10            1967

1   1967-11-29      0.026                                            11            1967

1    1967-12-29     0.027                                            12            1967

1    1968-01-29      0.01                     0.01                  1             1968

1    1968-02-29          .                                                2              1968

1    1968-03-29          .                                                3              1968

1     1968 -04-29        .                                                4              1968

And so on until the end of the year. I want the month and the year column filled up. 

I can then later replace the missing values with the return I want to assign. Could anyone please suggest a simplest way to accomplish this or provide a code? 

Looking forward to your reply. Please write if you need anymore information. I am using SAS 9.4

Regards 

Akarsh 

[Patrick]

@Akarsh91

Please provide in the future a SAS datastep creating the sample data. You're asking for other peoples expertise and time so I'd consider you doing this work instead us as an act of courtesy.

You're aligning all your dates to the 29th. Not sure what should happen with non-leap year Februaries. The code below will return the 28th for such cases.

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month year;
  format date date9.;
  datalines;
1,1967-10-29,0.025,,10,1967
1,1967-11-29,0.026,,11,1967
1,1967-12-29,0.027,,12,1967
1,1968-01-29,,0.01,1,1968
;
run;

data want;
  set have;
  by id;
  output;

  if last.id and month(date) ne 12 then
    do;
      call missing(Returns,Delisting_return);
      do while(month(date) ne 12);
        date=min(intnx('month',date,1,'b')+28 ,intnx('month',date,1,'e') );
        month=month(date);
        year=year(date);
        output;
      end;
    end;
run;

[Akarsh91]

@ Partick

Thank you patrick. The code worked and gave the desired output. I apologize for not providing the SAS datastep. However, the data I am using is downloaded from CRSP, so I didn't creat any datastep on my own. Anyways, thanks a lot.

[ballardw]

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.