BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shixin
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

7 REPLIES 7
Ksharp
Super User

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

Haikuo
Onyx | Level 15

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

data_null__
Jade | Level 19

Are you most interested in calculating the median or using the function?

shixin
Calcite | Level 5

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.

stat_sas
Ammonite | Level 13

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__
Jade | Level 19

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;

stat_sas
Ammonite | Level 13

@data_null_: Thanks so much for the suggestion. Agreed, it is better to use NWAY instead of where clause.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 13620 views
  • 3 likes
  • 5 in conversation