topic Re: How do I pick a value from one of two rows, by comparing two rows? in New SAS User

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

martino1 — Wed, 20 Feb 2019 17:30:05 GMT

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 🙂

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

Reeza — Wed, 20 Feb 2019 17:32:38 GMT

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 🙂

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

Astounding — Wed, 20 Feb 2019 17:35:30 GMT

An easy way...

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

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

martino1 — Wed, 20 Feb 2019 18:13:00 GMT

Thank you very much