Help in filling missing months

[pp2014]

I have following data:

data have;

informat dt yymmn6.;

input ID    prod  dt  val;

datalines;

10  A  201001  8

10  A  201003  9   

10  A  201005  2

10  A  201008  3

10  B  200903  1

10  B  200906  5

10  B  200909  2

10  B  200910  1

run;

I want to fill the missing dates(year/month).

Output wanted  is as follows:

id     prod          dt       val

10      A           201001     8

10      A           201002     .

10      A           201003     9

10      A           201004     .

10      A           201005     2

10      A           201006     .

10      A           201007     .

10      A           201008     3

10      B           200903     1

10      B           201004     .

10      B           201005     .

10      B           201006     5

10      B           201007     .

10      B           201008     .

10      B           201009     2

10      B           201010     1

Any help will be highly appreciated..

[pp2014]

I have tried something like below but it does not give the result I want.  Can anybody help me ??

data have;

informat dt yymmn6.;

input ID    prod $2.  dt  val;

datalines;

10  A  201001  8

10  A  201003  9  

10  A  201005  2

10  A  201008  3

10  B  200903  1

10  B  200906  5

10  B  200909  2

10  B  200910  1

run;

proc sort data = have;

  by id prod dt;

run;

data want(keep=id prod dt val);

  set have;

    by id prod dt;

  retain StartDate EndDate val;

  if first.prod then do;

    StartDate=dt;

  end;

  if last.prod then do;

    EndDate=dt;

        do dt=StartDate to EndDate;  

      output ;

        end; 

  end; output;

run;

proc print;

run;

[data_null__]

This is a popular technique

data have; 
   input ID prod :$1.  dt  val;
   informat dt yymmn6.;
   datalines;
10  A  201001  8
10  A  201003  9  
10  A  201005  2
10  A  201008  3
10  B  200903  1
10  B  200906  5
10  B  200909  2
10  B  200910  1
;;;;
   run;
data filled;
   set have end=eof;
   by id prod;
   output;
   if not eof then set have(firstobs=2 keep=dt rename=dt=ndt);
   if not last.prod then do;
      call missing(val);
      do m=1 to intck('MONTH',dt,ndt)-1;
         dt = intnx('MONTH',dt,1);
         output;
         end;    
      end;
   call missing(of ndt);
   format dt yymm.;
   drop ndt m;
   run;

[pp2014]

Thanks data_null_ for the solution.  It worked..

[pp2014]

One more question.   After running the code, In my dt field I am getting M, for example 2010M01.  Is there anyway I can get only 201001?

[pp2014]

I am sorry I changed the format to yymmn6. and it worked...

Thanks..

[pp2014]

Hi data_null_,

Somehow the above code is not working against my real data.  It does not fill up the missing dates instead it repeats the same the dates in the missing months.

Please let me know if how can I can send you the dataset of just 10 obs and 5 columns to test.

Thanks

[pp2014]

Hi Data_null_,

I figured  it out.. I will let you know if I need any help.

Thanks

[pp2014]

data_null_,

I need some help in adding missing dates.  I want the output following way based on the most current dt.  I want to fill the dates upto most current date(upto 12 months).

data have; 

   input ID prod :$1.  dt  val;

   informat dt yymmn6.;

   datalines;

10  A  201208  2

10  A  201212  3

10  B  201306  5

10  B  201309  2

10  B  201310  1

10  C  201402  9

10  C  201403  3

;;;;

   run;

   I want to fill the missing dates based on the most current date available which is  eg. 201403

Output wanted as follows based :

ID     prod     dt     val

10      A      201208   2

10      A      201209   .

10      A      201210   .

10      A      201211   .

10      A      201212   3

10      A      201301   .

10      A      201302   .

10      A      201303   .

10      A      201304   .

10      A      201305   .

10      A      201306   .

10      A      201307   .

10      A      201308   .

10      A      201309   .

10      A      201310   .

10      A      201311   .

10      A      201312   .

10      B      201309   2

10      B      201310   1

10      B      201311   .

10      B      201312   .

10      B      201401   .

10      B      201402   .

10      B      201403   .

10      C      201402   9

10      C      201403   3

[data_null__]

Does this do it?

data have; 
   input ID prod :$1.  dt  val;
   informat dt yymmn6.;
   datalines;
10  A  201001  8
10  A  201003  9  
10  A  201005  2
10  A  201008  3
10  B  200903  1
10  B  200906  5
10  B  200909  2
10  B  200910  1
;;;;
   run;
data filled;
   set have end=eof;
   by id prod;
   output;
   if not eof then set have(firstobs=2 keep=dt rename=dt=ndt);
   if not last.prod then do;
      call missing(val);
      do m=1 to intck('MONTH',dt,ndt)-1;
         dt = intnx('MONTH',dt,1);
         output;
         end;    
      end;
   if last.prod then do;
      call missing(val);
      do m = 1 to intck('MONTH',dt,today());
         dt = intnx('MONTH',dt,1);
         output;
         end;
      end;
   call missing(ndt);
   format dt yymm.;
   drop ndt m;
   run;
proc print;
   run;

[pp2014]

Everything works fine except that I want only

to fill the dates upto most current date(upto 12 months).  I don't want to fill the dates beyond 12 months..

[data_null__]

While(m le 12);

data have; 
   input ID prod :$1.  dt  val;
   informat dt yymmn6.;
   datalines;
10  A  201001  8
10  A  201003  9  
10  A  201005  2
10  A  201008  3
10  B  200903  1
10  B  200906  5
10  B  200909  2
10  B  200910  1
;;;;
   run;
data filled;
   set have end=eof;
   by id prod;
   output;
   if not eof then set have(firstobs=2 keep=dt rename=dt=ndt);
   if not last.prod then do;
      call missing(val);
      do m=1 to intck('MONTH',dt,ndt)-1;
         dt = intnx('MONTH',dt,1);
         output;
         end;    
      end;
   if last.prod then do;
      call missing(val);
      do m = 1 to intck('MONTH',dt,today()) while(m le 12);
         dt = intnx('MONTH',dt,1);
         output;
         end;
      end;
   call missing(ndt);
   format dt yymm.;
   drop ndt m;
   run;
proc print;
   run;

[pp2014]

Thanks a lot for the help...

[Ksharp]

data have;  
   input ID prod :$1.  dt  val;
   datalines; 
10  A  201001  8
10  A  201003  9   
10  A  201005  2
10  A  201008  3
10  B  200903  1
10  B  200906  5
10  B  200909  2
10  B  200910  1
;;;;
   run; 
   data want;
    merge have have(firstobs=2 keep=id prod dt rename=(id=_id prod=_prod dt=_dt));
     output;
     if id=_id and prod=_prod then do;
        do i=dt+1 to _dt-1;
         call missing(val);dt=i;output;
        end;
      end;
      drop _: i;
run;

Xia Keshan

Message was edited by: xia keshan