DATA Step, Macro, Functions and more

Help in filling missing months

Reply
Frequent Contributor
Posts: 127

Help in filling missing months

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

Frequent Contributor
Posts: 127

Re: Help in filling missing months

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;

Respected Advisor
Posts: 3,799

Re: Help in filling missing months

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;
Frequent Contributor
Posts: 127

Re: Help in filling missing months

Posted in reply to data_null__

Thanks data_null_ for the solution.  It worked..

Frequent Contributor
Posts: 127

Re: Help in filling missing months

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?

Frequent Contributor
Posts: 127

Re: Help in filling missing months

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

Thanks..

Frequent Contributor
Posts: 127

Re: Help in filling missing months

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

Frequent Contributor
Posts: 127

Re: Help in filling missing months

Hi Data_null_,

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

Thanks

Frequent Contributor
Posts: 127

Re: Help in filling missing months

Posted in reply to data_null__

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

Respected Advisor
Posts: 3,799

Re: Help in filling missing months

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;
Frequent Contributor
Posts: 127

Re: Help in filling missing months

Posted in reply to data_null__

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

Respected Advisor
Posts: 3,799

Re: Help in filling missing months

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;
Frequent Contributor
Posts: 127

Re: Help in filling missing months

Posted in reply to data_null__

Thanks a lot for the help...

Super User
Posts: 10,018

Re: Help in filling missing months

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

Ask a Question
Discussion stats
  • 13 replies
  • 877 views
  • 0 likes
  • 3 in conversation