Fluorite | Level 6

Apply floors to the dataset

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;

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Apply floors to the dataset

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.

5 REPLIES 5
Fluorite | Level 6

Re: Apply floors to the dataset

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

Re: Apply floors to the dataset

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

Re: Apply floors to the dataset

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.

Super User

Re: Apply floors to the dataset

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
```

Fluorite | Level 6

Re: Apply floors to the dataset

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.
Discussion stats
• 5 replies
• 405 views
• 0 likes
• 3 in conversation