topic Re: Apply floors to the dataset in SAS Programming

Apply floors to the dataset

fwu811 — Tue, 14 May 2024 11:38:41 GMT

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

Re: Apply floors to the dataset

fwu811 — Tue, 14 May 2024 12:08:20 GMT

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

Re: Apply floors to the dataset

fwu811 — Tue, 14 May 2024 12:09:13 GMT

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

FreelanceReinh — Tue, 14 May 2024 12:43:19 GMT

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.

Re: Apply floors to the dataset

Tom — Tue, 14 May 2024 15:16:43 GMT

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

Re: Apply floors to the dataset

fwu811 — Wed, 15 May 2024 09:39:01 GMT

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.