BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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

13 REPLIES 13
pp2014
Fluorite | Level 6

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__
Jade | Level 19

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
Fluorite | Level 6

Thanks data_null_ for the solution.  It worked..

pp2014
Fluorite | Level 6

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
Fluorite | Level 6

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

Thanks..

pp2014
Fluorite | Level 6

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
Fluorite | Level 6

Hi Data_null_,

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

Thanks

pp2014
Fluorite | Level 6

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__
Jade | Level 19

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
Fluorite | Level 6

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__
Jade | Level 19

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
Fluorite | Level 6

Thanks a lot for the help...

Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 3847 views
  • 0 likes
  • 3 in conversation