From start date and end date to a monthly panel

[RasmusT45]

Hi SAS Community

I am rather new to SAS.

I want to built a monthly panel data set from the following data structure:

Identifier	Start date	End date
X	01/01/1985	31/03/1985
Y	01/04/1999	30/04/2005

And the data set i want looks like this:

Identifier:	Month
X	January 1985
X	February 1985
X	March 1985
Y	April 1999
Y	May 1999
Y	...
Y	April 2005

I have tried some different approaches. However, none of them have seemed to work.

For example I have used the intnx-command to create a new variable with the next month prior to the startdate. But going from a new variable to a new observation have troubled me, so I hope you can help me.

Best regards Rasmus

[PeterClemmensen · Accepted solution]

How about this?

data have;
input Identifier $ (StartDate EndDate)(:ddmmyy10.);
format StartDate EndDate ddmmyy10.;
datalines;
X 01/01/1985 31/03/1985
Y 01/04/1999 30/04/2005
;

proc format; 
  picture dtfmt (default=30)
    other = '%B %Y' (datatype=date)
  ;
run;

data want;
   set have;
   Month = StartDate;
   output;
   do while (Month < EndDate);
      Month = intnx ('month', Month, 1, 'e');
      output;
   end;
   format month dtfmt.;
run;

[yabwon]

Hi,

something like this:

data have;
input Identifier $	Start_date : ddmmyy10.	End_date : ddmmyy10.;
format Start_date ddmmyy10.	End_date ddmmyy10.;
cards4;
X 01/01/1985 31/03/1985
Y 01/04/1999 30/04/2000
;;;;
run;

data want;
  set have;

  do _N_=0 by 1 until(_Month_ > End_date);
    _Month_ = intnx("month", start_date, _N_, "B");
    length Month $ 50; /* Month is se tup as text */
    Month = catx(" ", put(_Month_, Monname.), year(_Month_));
    output;
  end;
  keep Identifier Month;
run;
proc print;
run;

All the best

Bart