Desktop productivity for business analysts and programmers

how we insert rows between two rows that has different dates

Reply
Frequent Contributor
Posts: 87

how we insert rows between two rows that has different dates

I have the following dataset:

 

data have010;
infile cards truncover expandtabs;
input MC $ ET $ Date :date8. Time :time;
format Date date8.; /*format Time HHMM.;*/
cards;
US000323 Lath 13OCT12 19:37
US000323 Wheelinstall 25OCT12 0:00
US000323 Removal 31OCT12 0:00
US000323 Lath 31OCT12 14:03
US000323 Install 11JAN13 0:00
US000323 Lath 16MAR13 19:52
US000323 Lath 12OCT13 13:49
US000323 Lath 12OCT13 14:17
US000323 Removal 24MAY14 0:00
US000328 Lath 31JAN13 23:41
US000328 Creation 03FEB13 0:00
US000328 Lath 17MAY13 12:43
US000328 Lath 17MAY13 12:46
US000328 Lath 02SEP13 15:03
US000328 Creation 18JUL14 0:00
US000328 Lath 25SEP14 8:54
US000328 Install 15FEB15 0:00
US000328 Creation 21MAY15 0:00
US000328 WInstall 24JUN15 0:00
US000328 Lath 13JUL15 0:00
US000328 Creation 01OCT15 0:00
US000328 Lath 02JAN16 0:00
US000328 Lath 13JUN16 0:00
;
run;

What I want to do, is to insert rows between the rows so that result looks like something as below:

 

US000323 Lath 13OCT12 19:37
US000323 Meas 14OCT12 00:00
US000323 Meas 15OCT12 00:00
US000323 Meas 16OCT12 00:00
US000323 Meas 17OCT12 00:00
.
.
.
US000323 Wheelinstall 25OCT12 0:00
US000323 Meas 26OCT12 00:00
US000323 Meas 26OCT12 00:00
US000323 Meas 27OCT12 00:00
.
.
.
US000323 Removal 31OCT12 0:00

 

 

Super User
Super User
Posts: 7,668

Re: how we insert rows between two rows that has different dates

You can controll when the record gets written by using an output statement:

data want;
  set have;
  if <condition> then do;
    <do something>;
    output;
  end;
  else output;
run;

So this will check the if condition, if false then output the row, otherwise do something then output.  Your test data doesn't match your output by the way, so its not clear what your logic is.

Super User
Posts: 11,101

Re: how we insert rows between two rows that has different dates

In your actual project are there other variables in the data other than the 4 shown? If so what values should be assigned to those variables when the new records are inserted?

 

BTW you example data set code has errors reading the Time variable and the default length of ET is insufficient to hold the value of "Wheelinstall" which is truncated to 8 characters.

Respected Advisor
Posts: 4,802

Re: how we insert rows between two rows that has different dates

Use look ahead to do this. Make sure you read the last obs.

 

data have010;
length MC ET $12; 
input MC ET Date :date8. Time :time5.;
format Date date9. time time5.;
cards;
US000323 Lath 13OCT12 19:37
US000323 Wheelinstall 25OCT12 0:00
US000323 Removal 31OCT12 0:00
US000323 Lath 31OCT12 14:03
US000323 Install 11JAN13 0:00
US000323 Lath 16MAR13 19:52
US000323 Lath 12OCT13 13:49
US000323 Lath 12OCT13 14:17
US000323 Removal 24MAY14 0:00
US000328 Lath 31JAN13 23:41
US000328 Creation 03FEB13 0:00
US000328 Lath 17MAY13 12:43
US000328 Lath 17MAY13 12:46
US000328 Lath 02SEP13 15:03
US000328 Creation 18JUL14 0:00
US000328 Lath 25SEP14 8:54
US000328 Install 15FEB15 0:00
US000328 Creation 21MAY15 0:00
US000328 WInstall 24JUN15 0:00
US000328 Lath 13JUL15 0:00
US000328 Creation 01OCT15 0:00
US000328 Lath 02JAN16 0:00
US000328 Lath 13JUN16 0:00
;

data want;
set have010 end=done; by MC;
if not done then set have010(firstobs=2 keep=date rename=date=nextDate);
output;
if not last.MC then do;
    ET = "Meas"; time = '00:00't;
    date = intnx("DAY", date, 1);
    do while(date < nextDate);
        output;
        date = intnx("DAY", date, 1);
        end;
    end;
drop nextDate;
run;

PG
Super User
Posts: 9,854

Re: how we insert rows between two rows that has different dates

It is looking forward problem.


data have010;
infile cards truncover expandtabs;
input MC $ ET $ Date :date8. Time :time.;
format Date date8.; /*format Time HHMM.;*/
cards;
US000323 Lath 13OCT12 19:37
US000323 Wheelinstall 25OCT12 0:00
US000323 Removal 31OCT12 0:00
US000323 Lath 31OCT12 14:03
US000323 Install 11JAN13 0:00
US000323 Lath 16MAR13 19:52
US000323 Lath 12OCT13 13:49
US000323 Lath 12OCT13 14:17
US000323 Removal 24MAY14 0:00
US000328 Lath 31JAN13 23:41
US000328 Creation 03FEB13 0:00
US000328 Lath 17MAY13 12:43
US000328 Lath 17MAY13 12:46
US000328 Lath 02SEP13 15:03
US000328 Creation 18JUL14 0:00
US000328 Lath 25SEP14 8:54
US000328 Install 15FEB15 0:00
US000328 Creation 21MAY15 0:00
US000328 WInstall 24JUN15 0:00
US000328 Lath 13JUL15 0:00
US000328 Creation 01OCT15 0:00
US000328 Lath 02JAN16 0:00
US000328 Lath 13JUN16 0:00
;
run;
data want;
 merge have010 have010(keep=mc date rename=(mc=_mc date=_date) firstobs=2);
 output;
 if mc=_mc then do;
  do i=date+1 to _date-1;
   date=i;et='Meas';time=0;output;
  end;
 end;
drop i _:;
run;


Respected Advisor
Posts: 4,130

Re: how we insert rows between two rows that has different dates

[ Edited ]

My 5 cents

Append the additional records to the end your of your table, then sort it the way you need it for your report. Depending on your reporting needs the table could also be unsorted as many Proc's will return the result sorted based on the variables you use (unless you want to use by group processing which requires the source table to be pre-sorted).

 

Ask a Question
Discussion stats
  • 5 replies
  • 209 views
  • 2 likes
  • 6 in conversation