topic Re: Help in filling missing months in SAS Programming

Help in filling missing months

pp2014 — Wed, 07 May 2014 16:33:10 GMT

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

Re: Help in filling missing months

pp2014 — Wed, 07 May 2014 17:13:00 GMT

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;

Re: Help in filling missing months

data_null__ — Wed, 07 May 2014 17:25:48 GMT

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;

Re: Help in filling missing months

pp2014 — Wed, 07 May 2014 17:33:50 GMT

Thanks data_null_ for the solution. It worked..

Re: Help in filling missing months

pp2014 — Wed, 07 May 2014 17:37:14 GMT

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?

Re: Help in filling missing months

pp2014 — Wed, 07 May 2014 17:39:03 GMT

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

Thanks..

Re: Help in filling missing months

pp2014 — Wed, 07 May 2014 18:34:20 GMT

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

Re: Help in filling missing months

pp2014 — Wed, 07 May 2014 18:50:04 GMT

Hi Data_null_,

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

Thanks

Re: Help in filling missing months

Ksharp — Fri, 09 May 2014 13:13:57 GMT

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

Re: Help in filling missing months

pp2014 — Mon, 12 May 2014 15:29:44 GMT

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

Re: Help in filling missing months

data_null__ — Mon, 12 May 2014 16:18:39 GMT

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;

Re: Help in filling missing months

pp2014 — Mon, 12 May 2014 16:28:26 GMT

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

Re: Help in filling missing months

data_null__ — Mon, 12 May 2014 17:22:55 GMT

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;

Re: Help in filling missing months

pp2014 — Mon, 12 May 2014 17:28:42 GMT

Thanks a lot for the help...