topic Re: How to let the id of last row to be the new variable? in SAS Programming

How to let the id of last row to be the new variable?

HAHA888 — Tue, 19 Oct 2021 13:13:25 GMT

I want to compare the id is it same or not by comparing them in a same row.

And I tried this data step, Can someone help me :

data temp;
input id $ var1 var2;
New_id= id;
datalines;
aa 12 13
Bb 13 14
Cc 15 16
;

data want :

id	var1	var2	new id
aa	12	13
Bb	13	14	aa
Cc	15	16	Bb

Re: How to let the id of last row to be the new variable?

PeterClemmensen — Tue, 19 Oct 2021 13:21:51 GMT

data want;
   set temp;
   newid = lag(id);
run;

Re: How to let the id of last row to be the new variable?

Kurt_Bremser — Tue, 19 Oct 2021 13:29:42 GMT

For values of a previous observation, you use the LAG function:

new_id = lag(id);

Re: How to let the id of last row to be the new variable?

HAHA888 — Tue, 19 Oct 2021 13:37:25 GMT

Thank you so much!

But if now I want the new_id in the first row is equal to first id value (aa), what should I do?

id	var1	var2	new id
aa	12	13	aa
Bb	13	14	aa
Cc	15	16	Bb

Re: How to let the id of last row to be the new variable?

HAHA888 — Tue, 19 Oct 2021 13:38:30 GMT

Thank you!

But if now I want the new_id in the first row is equal to first id value (aa), what should I do?

id	var1	var2	new id
aa	12	13	aa
Bb	13	14	aa
Cc	15	16	Bb

Re: How to let the id of last row to be the new variable?

maguiremq — Tue, 19 Oct 2021 14:34:45 GMT

I think everyone's getting a little confused because your want data set keeps getting changed. Below is a cheap way to get what you want since the first record doesn't have a lag value to look for.

data want;
	set temp;
	new_id = lag(id);
	if missing(new_id) then new_id = id;
run;

id var1 var2 New_id 
aa 12 13 aa 
Bb 13 14 aa 
Cc 15 16 Bb

Can't guarantee this will hold up depending on the complexity of your actual data set.

Re: How to let the id of last row to be the new variable?

Kurt_Bremser — Tue, 19 Oct 2021 15:18:57 GMT

Just add an additional override:

data want;
set have;
new_id = lag(id);
if _n_ = 1 then new_id = id;
run;