topic Re: create new variables in SAS Programming

create new variables

kindbe17 — Mon, 24 Apr 2023 09:19:20 GMT

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;

Re: create new variables

PeterClemmensen — Mon, 24 Apr 2023 09:34:20 GMT

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.

Re: create new variables

Kurt_Bremser — Mon, 24 Apr 2023 10:10:18 GMT

This

select League. *,

is invalid SQL syntax.

Re: create new variables

kindbe17 — Mon, 24 Apr 2023 10:38:32 GMT

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

Re: create new variables

AhmedAl_Attar — Mon, 24 Apr 2023 10:49:40 GMT

@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

Re: create new variables

Kurt_Bremser — Mon, 24 Apr 2023 11:02:11 GMT

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.

Re: create new variables

ballardw — Mon, 24 Apr 2023 15:53:57 GMT

@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.