Apply floors to the dataset

[fwu811]

Hi,

I would like to apply two floors to the existing dataset below (Col1 values are sorted):

1) For any values in Col1 ❤️ replace their values in Col2 with that for Col1 =3;

1) For any values in Col1 >4 replace their values in Col2 with that for Col1 =4; 

Many thanks in advance.

data have;
input Col1 Col2;
datalines;
1 1
2 2
3 3
4 4
5 3
6 2

7 1
;

data want;
Col1 Col2;
1 3
2 3
3 3
4 4
5 4
6 4

7 4

[fwu811]

1) For any values in Col1 ❤️ replace their values in Col2 with that for Col1 =3;

[fwu811]

Updated:
1) For any values in Col1 less than 3 replace their values in Col2 with that for Col1 =3;

[FreelanceReinh · Accepted solution]

Hi @fwu811,

Try this:

data want;
if _n_=1 then do until(last);
  set have(where=(col1 in (3,4))) end=last;
  array v[3:4] _temporary_;
  v[col1]=col2;
end;
set have;
if .z<col1<3 then col2=v[3];
else if col1>4 then col2=v[4];
run;

Note that values <3 include missing values. If you really want to treat observations with missing Col1 the same way as those with Col1=2, Col1=1, etc., just delete the ".z<" part of the IF condition above.

[Tom]

So it does not matter what COL2 has already?  Then why did you include it?

What do you want to do with values from 3 to 4?  Or are you saying COL1 can only contain integers?

What do you want to do with missing values?

This code will convert anything less than 4 (including missing values) into 3 and everything else to 4.

data have;
  input Col1 Expect;
datalines;
1 3
2 3
3 3
4 4
5 4
6 4
7 4
;

data want;
  set have;
  if col1 < 4 then col2=3;
  else col2=4;
run;

Result

Obs    Col1    Expect    col2

 1       1        3        3
 2       2        3        3
 3       3        3        3
 4       4        4        4
 5       5        4        4
 6       6        4        4
 7       7        4        4

[fwu811]

The values in Col1 for applying a floor is set to by the user, and the floored value to Col2 is determined by the the corresponding Col2 for the Col1 value.