I am using SAS9.3 and was trying out the median function someone posted on this forum earlier on.
proc sql;
create table MEDIAN_SPEND as
SELECT GENDER
,AGE_GROUP
,SPEND
FROM FILE1
GROUP BY GENDER, AGE_GROUP
HAVING median(SPEND) = SPEND;
quit;
I still get the "The MEDIAN function has been called with only one argument." error message.
Can someone tell which part went wrong?
Thank you!
Upgrade to SAS 9.4 or to Use 2XDOW as below (not tested):
data want;
array spd (100) _temporary_;
do _n_=1 by 1 until (last.age_group);
set file1;
BY GENDER AGE_GROUP;
spd(_n_)=spend;
end;
median=median(of spd(*));
do _n_=1 by 1 until (last.age_group);
set file1;
BY GENDER AGE_GROUP;
if spend=median then
output;
end;
run;
Good luck,
Haikuo
If I remember right, You can only run MEDIAN() with sql under SAS9.4 . But there are lots of workaround to get it , like array ,Hash Table ,merge ......
proc mean
Upgrade to SAS 9.4 or to Use 2XDOW as below (not tested):
data want;
array spd (100) _temporary_;
do _n_=1 by 1 until (last.age_group);
set file1;
BY GENDER AGE_GROUP;
spd(_n_)=spend;
end;
median=median(of spd(*));
do _n_=1 by 1 until (last.age_group);
set file1;
BY GENDER AGE_GROUP;
if spend=median then
output;
end;
run;
Good luck,
Haikuo
Are you most interested in calculating the median or using the function?
Calculating the median. I thought I could use the function in my SQL since it's much easier but I just learnt that it's only available in SAS9.4.
proc means data=file1 median;
var spend;
class gender age_group;
output out=medians (where=(gender ne ' ' and age_group ne ' ') drop=_: ) median=median_spend;
run;
proc sql;
create table want as
select * from file1 a left join medians b
on a.gender=b.gender
and a.age_group=b.age_group
group by a.gender, a.age_group
having median_spend=spend;
quit;
Isn't this "where=(gender ne ' ' and age_group ne ' ')" just removing the one-way tables and wouldn't it be easier to specify NWAY option in the PROC statement?
stat@sas wrote:
proc means data=file1 median;
var spend;
class gender age_group;
output out=medians (where=(gender ne ' ' and age_group ne ' ') drop=_: ) median=median_spend;
run;proc sql;
create table want as
select * from file1 a left join medians b
on a.gender=b.gender
and a.age_group=b.age_group
group by a.gender, a.age_group
having median_spend=spend;
quit;
@data_null_: Thanks so much for the suggestion. Agreed, it is better to use NWAY instead of where clause.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: