Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

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

Hi..

 I have the following table...

 

MacCodeEvent_TypeDateTimeWcodeBcodeLoc
HPUS0001Lathing02JAN1319:24   
HPUS0001Creation29MAY130:007611HTELI1124P1
HPUS0001Creation31JAN140:007402HTELI1015P2
HPUS0001Lathing12JAN157:00   
HPUS000323Lathing13OCT1219:37   
HPUS000323Wheel install25OCT120:00   

 

 

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

 

MacCodeEvent_TypeDateTimeWcodeBcodeLoc
HPUS0001Creation01JAN130:00   
HPUS0001Lathing02JAN1319:24   
HPUS0001Creation29MAY130:007611HTELI1124P1
HPUS0001Creation31JAN140:007402HTELI1015P2
HPUS0001Lathing12JAN157:00   
HPUS000323Creation12OCT120:00   
HPUS000323Lathing13OCT1219:37   
HPUS000323Wheel install25OCT120: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


Accepted Solutions
Solution
‎11-04-2016 08:16 AM
Esteemed Advisor
Posts: 6,646

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎11-04-2016 08:16 AM
Esteemed Advisor
Posts: 6,646

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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

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.

Super Contributor
Posts: 305

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

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;

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 193 views
  • 2 likes
  • 4 in conversation