How do I pick a value from one of two rows, by comparing two rows?

[martino1]

Hello

Is there a way to create z1 with the value from x1 for a start, and then z2 with the value from z1, and z3 with the value from z2, but every time y changes z must again pick its value from x.

The problem is, that there are too many values in x, and I only need the first value from x every time y changes.

Data are sorted by column y.

In Excel it would be like this:

in cell z1:  =x1

in cell z2:  =if(y2=y1;z1;x2)

in cell z3:  =if(y3=y2;z2;x3)

in cell z4:  =if(y4=y3;z3;x4)

etc.

Data are big and I use SAS Enterprise Guide 7.11

For example:

Have:
	x	y	z
1	788	7
2	415	7
3	964	8
4	375	8
5	105	8
6	105	8
7	105	8
8	105	9
9	105	9
Want:
	x	y	z
1	788	7	788
2	415	7	788
3	964	8	964
4	375	8	964
5	105	8	964
6	105	8	964
7	105	8	964
8	105	9	105
9	105	9	105

Thank you 🙂

[Reeza · Accepted solution]

Yes, you can easily use BY group processing here. If you can't sort your data for some reason, you can use the NOTSORTED option. 

data want;
set have;

by Y NOTSORTED;

retain z;

if first.y then z = x;

run;

---

@martino1 wrote:

Hello

Is there a way to create z1 with the value from x1 for a start, and then z2 with the value from z1, and z3 with the value from z2, but every time y changes z must again pick its value from x.

The problem is, that there are too many values in x, and I only need the first value from x every time y changes.

Data are sorted by column y.

In Excel it would be like this:

in cell z1:  =x1

in cell z2:  =if(y2=y1;z1;x2)

in cell z3:  =if(y3=y2;z2;x3)

in cell z4:  =if(y4=y3;z3;x4)

etc.

Data are big and I use SAS Enterprise Guide 7.11

For example:

Have:
	x	y	z
1	788	7
2	415	7
3	964	8
4	375	8
5	105	8
6	105	8
7	105	8
8	105	9
9	105	9
Want:
	x	y	z
1	788	7	788
2	415	7	788
3	964	8	964
4	375	8	964
5	105	8	964
6	105	8	964
7	105	8	964
8	105	9	105
9	105	9	105

Thank you 🙂

---

[martino1]

Thank you very much

[Astounding]

An easy way...

Data want;
set have;
by y notsorted;
if first.y then z=x;
retain z;
run;