topic Re: Getting the oldest entry side by side with the new ones in SAS Data Management

Getting the oldest entry side by side with the new ones

Mahip — Mon, 17 Jun 2019 01:09:04 GMT

I have a dataset (enter) with id variable, and new and old entries for a product. I am trying to have the 'old' variable represent the oldest entry to the product. Currently, the 'old' variable represent an immediate old entry. Also, the variables may not be ordered as shown in the 'enter' dataset (3rd entry may be the fifth, for instance). The final dataset should look like the 'want' dataset as shown below. I tried creating a new variable to work around, but I am stuck at this. Thank you in advance!

data enter;
input id $ new $ old $;
datalines;
A 123P .
A 45X5 123P
A 678B 45X5
A 87E2 678B
A 95Y0 87E2
;
run;

data want;
input id $ new $ old $;
datalines;
A 123P .
A 45X5 123P
A 678B 123P
A 87E2 123P
A 95Y0 123P
;
run;

proc sql;
create table try as 
select *, case when old eq '' then new else old end as old_1
from enter
order by id;
quit;

Re: Getting the oldest entry side by side with the new ones

heffo — Mon, 17 Jun 2019 01:23:18 GMT

Would this work:

*Make sure it is sorted correctly. Might have to fix this according to your needs.;
proc sort data=enter out=have; 
	by id new;
run;

data want;
	set have;
	by id;
	length _old_original $4;
	retain _old_original; *Keep this variable over iterations of the indata.;
	if first.id then do;
		_old_original = new; *Save the original value in a temporary variable;
	end;
	else do;
		old = _old_original;
	end;
	drop _:; *Drop the temporary variable;
run;

Re: Getting the oldest entry side by side with the new ones

TomKari — Mon, 17 Jun 2019 01:28:54 GMT

Probably the same, but I had already written it!

data enter;
	input id $ new $ old $;
	datalines;
A 123P .
A 45X5 123P
A 678B 45X5
A 87E2 678B
A 95Y0 87E2
;
run;

data Want(drop=_:);
	length _Holdold $8;
	retain _Holdold;
	set enter;
	by id;

	if first.id then
		_Holdold = new;
	else old = _Holdold;
run;