how can I instert a row with specific data before first row based upon certain conditions

[imanojkumar1]

Hi..

 I have the following table...

MacCode	Event_Type	Date	Time	Wcode	Bcode	Loc
HPUS0001	Lathing	02JAN13	19:24
HPUS0001	Creation	29MAY13	0:00	7611	HTELI1124P	1
HPUS0001	Creation	31JAN14	0:00	7402	HTELI1015P	2
HPUS0001	Lathing	12JAN15	7:00
HPUS000323	Lathing	13OCT12	19:37
HPUS000323	Wheel install	25OCT12	0:00

I want to add another row to the above data in SAS EG  ... so that new data looks like:

MacCode	Event_Type	Date	Time	Wcode	Bcode	Loc
HPUS0001	Creation	01JAN13	0:00
HPUS0001	Lathing	02JAN13	19:24
HPUS0001	Creation	29MAY13	0:00	7611	HTELI1124P	1
HPUS0001	Creation	31JAN14	0:00	7402	HTELI1015P	2
HPUS0001	Lathing	12JAN15	7:00
HPUS000323	Creation	12OCT12	0:00
HPUS000323	Lathing	13OCT12	19:37
HPUS000323	Wheel install	25OCT12	0:00

So what is happening here is that first row of each MacCode (Sorted by Date and Time) should always have 'Event' as 'Creation' and 'Date' field should have A DAY BEFORE the date in the next row and Time as 00:00

Thanks in Advance....

Manoj

SAS beginner

[Kurt_Bremser · Accepted solution]

proc sort data=have;
by maccode descending date;
run;

data want;
set have;
by maccode;
output;
if last.maccode
then do;
  event_type = 'Creation';
  date = date - 1;
  time = 0;
  wcode = "";
  bcode = "";
  loc = "";
  output;
end;
run;

proc sort data=want;
by maccode date;
run;

[RW9]

Am not typing that test data in, so this code is untested (post test data as a datastep).  

The principal is sort the data in descending order, so the first record appears last.  Then a datastep:

data want;
  set have;
  by maccode;
  if last.maccode then do;
    output;
    /* set your replacement values here */
    output;
  end;
  else output;
run;

This outputs all the data, and at the last maccode outputs an additional record.  You can then re-sort the data back to original.  You could also do it with the current sort order, but then you would need to put data into temporary variables, just a bit more fiddly.

[Loko]

Assuming your data is already sorted:

data want;
set have;

by MacCode Date Time;

if first.MacCode then
 do;
	output;
	_MacCode=MacCode;
	_Date=Date-1;
	call missing(of MacCode--Loc);
	MacCode=_MacCode;
	Event_Type='Creation';
	Date=_Date;
	time='0:00't;
	end;
output;


drop _MacCode _Date;
run;