topic Re: return conditional max value across columns in SAS Data Management

return conditional max value across columns

Miracle — Wed, 11 May 2016 09:39:08 GMT

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.

Re: return conditional max value across columns

RW9 — Wed, 11 May 2016 10:01:06 GMT

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.

Re: return conditional max value across columns

Miracle — Wed, 11 May 2016 10:10:54 GMT

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.

Re: return conditional max value across columns

RW9 — Wed, 11 May 2016 10:16:08 GMT

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.

Re: return conditional max value across columns

Miracle — Mon, 16 May 2016 01:11:36 GMT

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;