BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mahip
Obsidian | Level 7

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;



1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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;

View solution in original post

2 REPLIES 2
heffo
Pyrite | Level 9

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;
TomKari
Onyx | Level 15

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 493 views
  • 1 like
  • 3 in conversation