Hi all,
I'm wondering whether there is a way to calculate percentile across variables for each observation, and excludes missing values in the calculation?
The percentile function seems to work but if there is just a single missing value within all the variables then the result is a missing variable.
Only workaround I was able to find was to achieve this in Excel, as my dataset fortunately wasn't too large.
Thanks for your help!
Thanks!
The corrected code is below and still does not replicate the problem:
data test;
array pct(100) pct1-pct100 (1:100);
x=pctl(50, of pct(*));
do i=20 to 30;
pct(i)=.;
end;
y=pctl(50, of pct(*));
z=pctl(50, of pct1--pct100);
q=pctl(50, pct1, pct2, pct3, pct21, pct25, pct29, pct30, pct50, pct99, pct100);
keep x y z q;
run;
proc print;run;
@tts Have you been able to replicate your issue?
I would recommend a transpose of your data and then using a more robust procedure such as proc univariate or proc means to calculate the percentile.
However, the behaviour your describing is not consistent with the percentile documentation, which states:
The PCTL function returns the percentile of the nonmissing values corresponding to the percentage. I can't replicate that behaviour as well, can you post a sample of your data where this was occuring?
data test;
array pct(100) pct1-pct100 (1:100);
x=pctl(50, of pct(*));
do i=20 to 30;
pct(i)=.;
end;
y=pctl(50, of pct(*));
keep x y;
run;
If you can define it that way, you could also define your array similarily.
array stocks(*) A--VLU;
At any rate, I still can't replicate your issue. Please post your code and sample data that replicates your problem. I'm guessing you actually have some other issue.
data test;
array pct(100) pct1-pct100 (1:100);
x=pctl(50, of pct(*));
do i=20 to 30;
pct(i)=.;
end;
y=pctl(50, of pct(*));
z=pctl(95, pct1--pct100);
keep x y z;
run;
proc print;run;
Hi @Reeza,
I think you should insert an "of" into your definition of z. Otherwise, the 95th percentile of a single value (singleton set), pct1--pct100=pct1+pct100=101, will be calculated.
Thanks!
The corrected code is below and still does not replicate the problem:
data test;
array pct(100) pct1-pct100 (1:100);
x=pctl(50, of pct(*));
do i=20 to 30;
pct(i)=.;
end;
y=pctl(50, of pct(*));
z=pctl(50, of pct1--pct100);
q=pctl(50, pct1, pct2, pct3, pct21, pct25, pct29, pct30, pct50, pct99, pct100);
keep x y z q;
run;
proc print;run;
@tts Have you been able to replicate your issue?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.