Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- How do I create a new variable using an if then statement for above me...

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-08-2023 12:44 PM
(377 views)

How do I create a new variable using an if then statement for above median and below median roi value? I want to create a new column using data syntax, that will show value 1 for ROIs above median roi, otherwise 0.

So far I got to this point below:

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

Welcome to the Communities forum.

If I've understood your requirements correctly, does the following help (essentially the last step creates the new column, the rest is just set up):

```
/* set up sample roi data */
data have;
input roi;
datalines;
1
2
3
4
5
;
/* set up median roi value */
%let median_roi = 3;
data want;
set have;
/* use greater than comparison operator to assign 1 (true) or 0 (false) */
new_column = roi gt &median_roi;
run;
```

If you need something different, then please show what results you are looking for for each row and confirm the logic behind it.

Thanks & kind regards,

Amir.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Use PROC RANK and 2 ranks to split the data?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
proc rank data=modtwo out=want groups=2 ties=low;
var roi;
ranks rankROI;
run;
```

@prn21005 wrote:

How do I create a new variable using an if then statement for above median and below median roi value? I want to create a new column using data syntax, that will show value 1 for ROIs above median roi, otherwise 0.

So far I got to this point below:

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

First, realize that you will eventually need to deal with realistic questions. What should happen with ROI:

- Is exactly equal to the median?
- Has a missing value?

Here is an approach that extends tools that you already know, to get the result:

```
proc summary data=have;
var roi;
output out=medians (keep=roi_median) median=roi_median;
run;
```

That gives you the median in a data set, which can be used in a subsequent step.:

```
data want;
set have;
if _n_=1 then set medians;
roi_group = roi >= roi_median;
drop roi_median;
```

run;

Note that the logic can easily be expanded to compute/group for more than one variable, without increasing the number of steps. For example, you could begin with:

```
proc summary data=have;
var roi margin turnover;
output out=medians (keep=roi_median margin_median turnover_median) median=roi_median margin_median turnover_median;
run;
```

Also note that the assignment statement that computes roi_group could have been accomplished with IF/THEN statements, but would take more code:

```
if roi >= roi_median then roi_group=1;
else roi_group = 0;
```

But the assignment statement shown above accomplishes the same with a shorter program.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.