Value in last row

[Ronein]

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:

1

1

1

1

1

Expected values in Newfield for customer 2 are:

0

0

0

0

0

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;

[RW9]

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;

[Ksharp]

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;

[Astounding]

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.