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
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.