BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
fwu811
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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.

View solution in original post

5 REPLIES 5
fwu811
Fluorite | Level 6
1) For any values in Col1 ❤️ replace their values in Col2 with that for Col1 =3;
fwu811
Fluorite | Level 6
Updated:
1) For any values in Col1 less than 3 replace their values in Col2 with that for Col1 =3;
FreelanceReinh
Jade | Level 19

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
Super User Tom
Super User

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
Fluorite | Level 6
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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 405 views
  • 0 likes
  • 3 in conversation