topic How to Create extra rows with missing values? in SAS Programming

How to Create extra rows with missing values?

Akarsh91 — Tue, 30 May 2017 20:10:35 GMT

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

Re: How to Create extra rows with missing values?

Patrick — Thu, 01 Jun 2017 22:26:23 GMT

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

Re: How to Create extra rows with missing values?

Akarsh91 — Tue, 30 May 2017 23:41:36 GMT

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

Re: How to Create extra rows with missing values?

ballardw — Thu, 01 Jun 2017 20:29:25 GMT

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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.