How to convert data set containing observation with start date and end date in to multiple monthly divided observations

[mrug12]

I have  data set containing start date and end date.

I want to convert the data set into monthly divide basis.

So I want to convert each record in to multiple record devide according monthly basis.

I would like to have few suggestion as well papers if you can refer

[yonib]

Hi mrug12,

Can you i provide an example of your source data and the way you want the resulting file to look

[mrug12]

Hii,

Here is example for the source data

x y z sdate edate

1 1 1 jan09 mar11

2 2 2 oct10 aug11

Now I want to convert it as below

x y z months

1 1 1 jan09

1 1 1 feb09

.

.

.

1 1 1 mar11

2 2 2 oct10

2 2 2 nov10

.

.

.

.

2 2 2 aug11

here is basic deatils for it

[data_null__]

I've been learning PROC EXPAND, trying at least.  While this adds nothing to the solution posted by Tom it is mildly interesting to me. 

It appears that PROC EXPAND needs a purpose other than expanding the obs, because without a numeric variable it prints the message.

NOTE: Nothing to do.

Here the variable "I" gives PROC EXPAND a purpose.

Note the data has to have start and end dates as observations.

data have;
   input x y z @;
   do i=1 to 2;
      input date :monyy. @;
      output;
      end;
   format date monyy.;
   cards;
1 1 1 jan09 mar11
2 2 2 oct10 aug11
;;;;
   run;
proc print;
   run;
proc expand to=month method=none;
   by x y z;
   id date;
   format date monyy.;
   run;
proc print;
   run;

Obs    x    y    z    date     i
  1    1    1    1    JAN09    1
  2    1    1    1    FEB09    .
  3    1    1    1    MAR09    .
  4    1    1    1    APR09    .
  5    1    1    1    MAY09    .
  6    1    1    1    JUN09    .
  7    1    1    1    JUL09    .
  8    1    1    1    AUG09    .
  9    1    1    1    SEP09    .
 10    1    1    1    OCT09    .
 11    1    1    1    NOV09    .
 12    1    1    1    DEC09    .
 13    1    1    1    JAN10    .
 14    1    1    1    FEB10    .
 15    1    1    1    MAR10    .
 16    1    1    1    APR10    .
 17    1    1    1    MAY10    .
 18    1    1    1    JUN10    .
 19    1    1    1    JUL10    .
 20    1    1    1    AUG10    .
 21    1    1    1    SEP10    .
 22    1    1    1    OCT10    .
 23    1    1    1    NOV10    .
 24    1    1    1    DEC10    .
 25    1    1    1    JAN11    .
 26    1    1    1    FEB11    .
 27    1    1    1    MAR11    2
 28    2    2    2    OCT10    1
 29    2    2    2    NOV10    .
 30    2    2    2    DEC10    .
 31    2    2    2    JAN11    .
 32    2    2    2    FEB11    .
 33    2    2    2    MAR11    .
 34    2    2    2    APR11    .
 35    2    2    2    MAY11    .
 36    2    2    2    JUN11    .
 37    2    2    2    JUL11    .
 38    2    2    2    AUG11    2

[mrug12]

Thanks for the reply, It is also right way,but as you see i like tom's way it is more smaller code for all.

[Tom · Accepted solution]

You could write a data step to do this.  Possibly you could also use proc expand.

Works easily if your EDATE and SDATE variables are actual SAS date values

data want;

   set have;

  format month date9.;

  do i=0 by 1 while (month < edate) ;

    month = intnx('month',sdate,i) ;

    output;

  end;

run;

[mrug12]

Thanks Tom ,

It is really help full.