First available variable to select

[Ronein]

Hello

I have a data set with percentiles information.

Some of the percentiles are not always available due to data structure .

I have the information in long and way (both ).

I want to select from data set "want" the 19th percentile and if it doesnt exist then 20th percentile and if it doesnt exist then 21th percentile and so on.

What is the way to do it please?

I know coalesce  but here it is not working because coalesce is working for existing  variables 

Data have;
input Percentile value;
cards;
0  13.9
1 14.1
2 14.2
3 14.4
4 14.7
5 15.1
6 15.4
7 15.5
8 15.7
12 16.0
14 16.1
17 16.8
18 17.5
20 18.2
21 19.1
22 20.1
23 24.1
24 25.0
25 26.3
;
Run;

proc transpose data=have  out=want prefix=p;
var value;
id  Percentile;
Run;

[ballardw]

You don't show anything about how you expect the output data set to look like.

Coaslesce works just fine:

proc transpose data=have  out=trans prefix=p;
var value;
id  Percentile;
Run;

data want;
   set trans;
   wantedvalue = coalesce(p19,p20,p21,p22, p23,p24,p25);
run;

When P19 doesn't exist, or more variables in the data set from transpose the use in the Coalesce function 1) creates the variable, 2) with a missing value and 3) result is the first of the populated variables.

Or add Percentile 19, or any other tiles, with missing for value. Then the transposed set will have P19 or whatever, with a missing value so you don't even get the note about variable Pxx is uninitialized.

[Kurt_Bremser]

No transposing needed, RETAIN and COALESCE do it with the initial dataset:

data want;
set have end=done;
retain wanted_value;
if percentile ge 19 then wanted_value = coalesce(wanted_value,value);
if done;
keep wanted_value;
run;

---

@Ronein wrote:

Hello

I have a data set with percentiles information.

Some of the percentiles are not always available due to data structure .

I have the information in long and way (both ).

I want to select from data set "want" the 19th percentile and if it doesnt exist then 20th percentile and if it doesnt exist then 21th percentile and so on.

What is the way to do it please?

I know coalesce  but here it is not working because coalesce is working for existing  variables 

Data have;
input Percentile value;
cards;
0  13.9
1 14.1
2 14.2
3 14.4
4 14.7
5 15.1
6 15.4
7 15.5
8 15.7
12 16.0
14 16.1
17 16.8
18 17.5
20 18.2
21 19.1
22 20.1
23 24.1
24 25.0
25 26.3
;
Run;

proc transpose data=have  out=want prefix=p;
var value;
id  Percentile;
Run;

---

[s_lassen]

Hi Kurt,

I think your already simple solution can be made a bit simpler (and faster), for instance like this:

data want;
  set have(keep=value);
  where value ge 19;
  output;
  stop;
  rename value=wanted_value;
run;

Not sure if this is exactly what @Ronein is looking for, but it should produce the same as your data step code.

[Kurt_Bremser]

Good idea, one slight correction:

data want (keep=wanted_value);
  set have;
  where percentile ge 19;
  output;
  stop;
  rename value=wanted_value;
run;

[PaigeMiller]

You are working very hard to try to force an answer to a question (this is at least your third thread on the issue) that really doesn't have an answer. Because of ties in your data, there is no 19th percentile. So yes, you can force it to be equal to some other percentile, now the 19th percentile is equal to the 20th percentile or the 21st percentile, which you already knew from your earlier threads. This is easily available information, this is what PROC RANK was telling you, and it's a lot easier to obtain that information from PROC RANK.

So what have you gained from all these attempts to force a value for the 19th percentile? In my opinion, you are no further ahead than you were with PROC RANK. If there is no 19th percentile because of ties, which is what PROC RANK is telling you, then you have not gained anything here by forcing a value for it.

[PGStats]

If, for some reason, you wanted to do this in SQL:

proc sql;
create table want as
select value as wanted_value
from have where Percentile ge 19
having Percentile = min(Percentile);
quit;