create new variables

[kindbe17]

Hi everyone, could you please help me to use proc sql, create new variables (not macro) that will keep N, Mean, STD, Median, Min, Max of SALARY by LEAGUEs from sashelp.BASEBALL dataset.

i tried this but seems i should have not use proc means;

proc means data=sashelp.baseball;

output out=baseball_1 N=N1 Mean=Mean1 STD=STD1 Median=Median1 Min=Min1 Max=Max1;

class league;

var salary;

run;

proc sql;

create table baseball_2 as

select League. *,

N1 as N,

Mean1 as Mean,

STD1 as STD,

Median1 as Median,

Min1 as Min,

Max1 as Max

FROM baseball_1;

quit;

[PeterClemmensen]

Do you want to create the statistics in PROC SQL or simply keep the variables that PROC SUMMARY produces?

In the second case, there is no need to use PROC SQL at all. Simply use a keep = option in the PROC SUMMARY Output Statement.

[kindbe17]

yes, i want to create the statistics in Proc SQL.

[AhmedAl_Attar]

@kindbe17 

I believe this is what you are looking for

proc sql;
	create table baseball_sql as
	select league,
		n(salary) as N,
		Mean(salary) as Mean,
		STD(salary) as STD,
		Median(salary) as Median,
		Min(salary) as Min,
		Max(salary) as Max
	FROM sashelp.baseball
	Group by league
	Order by league;
quit;
/* vs. */
proc means data=sashelp.baseball NWAY missing;
	output out=baseball_means(drop=_:) N=N1 Mean=Mean1 STD=STD1 Median=Median1 Min=Min1 Max=Max1;
	class league;
	var salary;
run;

Hope this helps

[Kurt_Bremser · Accepted solution]

proc sql;
create table baseball_2 as
  select
    league,
    min(salary) as min
  from sashelp.baseball
  group by league
;
quit;

Add other SQL summary functions as documented here.

[Kurt_Bremser]

This

select League. *,

is invalid SQL syntax.

[ballardw]

---

@kindbe17 wrote:

Hi everyone, could you please help me to use proc sql, create new variables (not macro) that will keep N, Mean, STD, Median, Min, Max of SALARY by LEAGUEs from sashelp.BASEBALL dataset.

i tried this but seems i should have not use proc means;

---

Why not? From the SQL you show I would think that:

proc means data=sashelp.baseball;
  output out=baseball_1 (drop=_type_ _freq_)
        N=N Mean=Mean STD=STD Median=Median Min=Min Max=Max
  ;
  class league;
  var salary;
run;

Would be sufficient.

If you don't actually want the total row for all leagues then add NWAY to the Proc Means statement.