topic Re: first or last in a row in SAS Programming

first or last in a row

mt88 — Tue, 17 Nov 2020 13:24:33 GMT

Hello,

I'm looking for a function that would return the first value in a row of variables and one that will return that last value in the row. For example, if I have a data set like this:

var1	var2	var3	var4	var5	var6	var7	var8
2	1	7	4	3	5	6	9
4	6	10	15	23	2	10	0
15	22	6	4	2	98	1	20

I'd like to create two new variables (call them first and last) that have the first and last values in each row, so I'd end up with this (relevant values colored for clarity):

var1	var2	var3	var4	var5	var6	var7	var8	first	last
2	1	7	4	3	5	6	9	2	9
4	6	10	15	23	2	10	0	4	0
15	22	6	4	2	98	1	20	15	20

Any help would be appreciated.

Thank you.

Re: first or last in a row

PeterClemmensen — Tue, 17 Nov 2020 13:39:21 GMT

data have;
input var1-var8;
datalines;
2 1 7 4 3 5 6 9
4 6 10 15 23 2 10 0
15 22 6 4 2 98 1 20
;

data want;
   set have;
   array a {*} var1-var8;
   first = a[lbound(a)];
   last  = a[hbound(a)];
run;

Result:

var1 ............... var8  first last 
2  1  7  4  3  5  6  9     2     9 
4  6  10 15 23 2  10 0     4     0 
15 22 6  4  2  98 1  20    15    20

Re: first or last in a row

novinosrin — Tue, 17 Nov 2020 13:39:14 GMT

 
data have;
 input var1	var2	var3	var4	var5	var6	var7	var8;
 cards;
2	1	7	4	3	5	6	9
4	6	10	15	23	2	10	0
15	22	6	4	2	98	1	20
;

data want;
 set have;
 array v var1-var8;
 first=v(1);
 last=v(dim(v));
run;

Re: first or last in a row

mt88 — Tue, 17 Nov 2020 13:58:33 GMT

Thank you, that worked. As a follow up, I'm wondering how to do something similar when there is missing data present. For example, starting with a similar data set, except I've added in some missings:

var1	var2	var3	var4	var5	var6	var7	var8
.	1	7	.	3	5	6	9
4	6	.	15	.	2	10	0
15	22	6	.	2	98	1	.

I'd like to end up with a similar results, except where the missing values are not included (relevant values colored for clarity):

var1	var2	var3	var4	var5	var6	var7	var8	first	last
.	1	7	.	3	5	6	9	1	9
4	6	.	15	.	2	10	0	4	0
15	22	6	.	2	98	1	.	15	1

Thank you.

Re: first or last in a row

novinosrin — Tue, 17 Nov 2020 14:06:55 GMT

I am lazy , here is my lazy solution-


data have;
input var1	var2	var3	var4	var5	var6	var7	var8;
cards;
.	1	7	.	3	5	6	9
4	6	.	15	.	2	10	0
15	22	6	.	2	98	1	.
;


data want;
 set have;
 array v var1-var8;
 array vv var8-var1;
 first=coalesce(of v(*));
 last=coalesce(of vv(*));
run;

I was spoiled by @data_null__ teaching me the reverse technique 🙂

Re: first or last in a row

PeterClemmensen — Tue, 17 Nov 2020 14:07:06 GMT

data have;
input var1-var8;
datalines;
. 1 7 . 3 5 6 9
4 6 . 15 . 2 10 0
15 22 6 . 2 98 1 .
;

data want;
   set have;
   first = coalesce(of var1-var8);
   last = coalesce(of var8-var1);
run;

Edited post from brute force to this 🙂

Re: first or last in a row

mt88 — Tue, 17 Nov 2020 14:40:59 GMT

Thank you all of the help!