BookmarkSubscribeRSS Feed
imanojkumar1
Quartz | Level 8

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

 

 

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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.

PGStats
Opal | Level 21

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


Patrick
Opal | Level 21

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

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 958 views
  • 2 likes
  • 6 in conversation