topic Value in last row in SAS Programming

Value in last row

Ronein — Tue, 27 Nov 2018 08:20:24 GMT

Hello

For each customer there are multiple rows.

I want to add a new field (binary variable that get value 1 or 0)

IF the value of X in last row of customer is equal or greater than 1 then newField=1;

else newField=0;

Expected values in Newfield for customer 1 are:

Expected values in Newfield for customer 2 are:

Why the code below is not giving the wanted result?

data c1;
input ID x;
cards;
1 0
1 0
1 1
1 2
1 3
2 0 
2 0 
2 0 
2 0 
2 0 
;
run;


data c2;
set c1;
IF last.ID and x>=1 then Newfield=1;else Newfield=0;
run;

Re: Value in last row

RW9 — Tue, 27 Nov 2018 09:01:16 GMT

What is the sorting variables for that data. The simplest method to get what you want is to reverse sort the data - so the last record per id is first, then retain that down the data, e.g.:

data want;
  set have;
  retain x;
  if first.id then new_x=ifn(x>1,1,0);
run;

Then sort back to what it is.

If you can't sort your data - and if you cant then your data is not good for working with - then you will need to merge the result back on, something like:

data inter;
  set have;
  by id;
  if last.id then output;
run;

data want (drop=base);
  merge have inter (rename=(x=base));
  by id;
  x=ifn(x>1,1,0);
run;

Re: Value in last row

Ksharp — Tue, 27 Nov 2018 12:35:51 GMT

data c1;
input ID x;
cards;
1 0
1 0
1 1
1 2
1 3
2 0 
2 0 
2 0 
2 0 
2 0 
;
run;

data want;

do until(last.id);
 set c1;
 by id;
end;
if x>=1 then Newfield=1;else Newfield=0;
do until(last.id);
 set c1;
 by id;
 output;
end;
run;

Re: Value in last row

Astounding — Tue, 27 Nov 2018 14:24:43 GMT

Your code is very close to working. It is missing the BY statement in the DATA step. The BY statement is what creates first.ID and last.ID:

data c2;
set c1;
by id;
IF last.ID and x>=1 then Newfield=1;else Newfield=0;
run;

Of course, the data must be in order to permit the BY statement. In your sample data, that is not a problem. But sometimes you need to run PROC SORT first.