help with finding median

[saslackey]

this is my table

region price sales
1 23 45
1 34 456
1 233 3465
1 65 34
2 23 3545
2 67 5653
i need to create a new table with

region median(price) sum(sales)


can someone tell me how to do that?

[Cynthia_sas]

Hi:
Depending on whether you want a report or an output dataset, SAS procedures that could give you that type of information is PROC MEANS, PROC TABULATE or PROC REPORT.

cynthia

[saslackey]

no actually i am writing a proc sql which i need help with. let me show you a small piece of my code.
finally2 is the table name

proc SQL;
Create Table last
(
REGION NUM,
sum_sales DEC,
median_price DEC );
Quit;

proc sql;
insert into last
SELECT finally2.REGION,
SUM(finally2.sales) As Sum_sales,
______ as median_price
FROM finally2
Group by finally2.REGION;
quit;
run;

[Cynthia_sas]

It looks like a report with a summary line to me. I would be tempted to use PROC REPORT with an RBREAK statement. See related post and program example here:
http://support.sas.com/forums/thread.jspa?threadID=11006&tstart=0

cynthia

[Reeza]

MS SQL does not have a built in median function. Even if SAS was going to pass through the function somehow the data would have to come back to your computer /SAS server to be processed by SAS.

Other SQL servers may, I'm not familiar with them.

If you're using SAS you're much better off using a proc tabulate or proc report or Proc Means.

proc sort data=sashelp.prdsale; by region; run;

Proc means data=sashelp.prdsale noprint;
by region;
var actual;
output out=last median(actual)=median_price sum(actual)=sum_sales;
run;