turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- return conditional max value across columns

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2016 05:39 AM

Hi there.

Can I please ask how can I return a conditional max value across columns?

Let say I have a=1, b=10, c=98, d=99, e=99, f=98, g=20, h=.

and I would like another new column i.e. max=20.

Thank you very much.

Accepted Solutions

Solution

05-15-2016
09:12 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2016 08:57 PM - edited 05-15-2016 09:11 PM

Hi @RW9.

Sorry for the late reply and failing to make the question clearer in the first place.

Just ran your code and with a little change I managed to get it worked.

Again thank you very much.

```
data want;
a=1; b=10; c=98; d=99; e=98; f=98; g=20; h=.;
array rep{8} a b c d e f g h;
array act{8};
do i=1 to 8;
act{i}=ifn(rep{i}>90,.,rep{i});
end;
new_var=max(of act[*]);
drop i act:;
run;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to wong

05-11-2016 06:01 AM

Something like:

new_variable=max(a,b,c,d,e,f,g,h);

For better code, please post test data in the form of a datastep covering all permutations, and what the output should look like.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2016 06:10 AM

Hi @RW9 thank you very much for the quick reply.

new_variable=99 if I use new_variable=max(a,b,c,d,e,f,g,h) when numeric variables of a=1, b=10, c=98, d=99, e=99, f=98, g=20, h=.

But I need 20 to be returned given this particular case only in a datastep.

Thank you very much.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to wong

05-11-2016 06:16 AM

This is why I ask for test data and required output. I can't see any logical reason why 20 would be the maximum value from that set of variables:

a=1, b=10, c=98, d=99, e=99, f=98, g=20, h=.

Do you mean anything 90 or above is missing? Then maybe:

data want; a=1; b=10; c=98; d=99; e=98; f=98; g=20; h=.; array rep{7} a b c d e f g h; array act{7}; do i=1 to 7; act=ifn(rep{i}>90,.,rep{i}); end; new_var=max(of act); run;

I don't have access to SAS at the moment to test the code, but something like that should work.

Solution

05-15-2016
09:12 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2016 08:57 PM - edited 05-15-2016 09:11 PM

Hi @RW9.

Sorry for the late reply and failing to make the question clearer in the first place.

Just ran your code and with a little change I managed to get it worked.

Again thank you very much.

```
data want;
a=1; b=10; c=98; d=99; e=98; f=98; g=20; h=.;
array rep{8} a b c d e f g h;
array act{8};
do i=1 to 8;
act{i}=ifn(rep{i}>90,.,rep{i});
end;
new_var=max(of act[*]);
drop i act:;
run;
```