proc sql help pls

[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?



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;

[Doc_Duke]

Sorry, you can't. The MEDIAN function in SQL only works on the current row. You can do what you want with PROC MEANS.

[Cynthia_sas]

Hi:
You can create both reports and output datasets from PROC REPORT, PROC TAUBULATE and PROC MEANS. You will have to decide which one provides the structure of the output data that you desire. Sample program below makes some fake data (based on your post) and adds some regions so there are 4 regions instead of 2.

cynthia
[pre]
data sales;
infile datalines;
input Region Price Sales;
return;
datalines;
1 23 45
1 34 456
1 233 3465
1 65 34
2 23 3545
2 67 5653
3 53 245
3 68 336
3 133 2365
4 44 88
4 46 34
4 144 2444
4 54 79
4 49 50
4 204 1444

;
run;

ods html file='c:\temp\median_report.html' style=sasweb;
proc report data=sales nowd split='/'
out=work.repout;
title '1a) Proc Report';
column region n price sales;
define region / group;
define n / 'Count';
define price / median 'Median/Price' f=comma8.;
define sales / sum 'Sum/Sales' f=comma8.;
rbreak after / summarize;
run;

proc tabulate data=sales f=comma8.
out=work.tabout;
title '1b) Proc Tabulate';
var price sales;
class region;
table region all,
all*n price*median sales*sum;
keylabel n='Count'
all='Total';
run;

proc means data=sales n median sum;
title '1c) Proc Means';
class region;
var price sales;
output out=work.mnout n=Count
median(price)=MedPrice
sum(sales)=SumSales;
run;

ods _all_ close;

ods html file='datasets.html' style=sasweb;
proc print data=repout;
title '2a) Dataset created by PROC REPORT';
run;

proc print data=tabout;
title '2b) Dataset created by PROC TABULATE';
run;

proc print data=mnout;
title '2c) Dataset created by PROC MEANS';
run;
ods _all_ close;
title;
ods listing;
[/pre]