topic Re: How to use "BY" or the like in PROC SQL ? in SAS Programming

How to use "BY" or the like in PROC SQL ?

Phil_NZ — Sun, 07 Mar 2021 08:27:51 GMT

Hi everyone!

A part of my dataset agg_amihud_vw is as below:

LOC	year	_TYPE_	_FREQ_	agg_amh_vw
ARG	1999	3	    13	    .
ARG	2000	3	    12	    0.0000227405
ARG	2001	3	    11	    0.0002002287
AUS	1999	3	    116	    .
AUS	2000	3	    99	    0.0000199988
AUS	2001	3	    90	    0.0000241868
AUT	1999	3	    26	    .
AUT	2000	3	    24	    0.0001970891
AUT	2001	3	    21	    0.0002672976
BEL	1999	3	    62	    .
BEL	2000	3	    53	    0.0002912877
BEL	2001	3	    50	    0.0002791486

what I want is to calculate means of agg_amihud_vw through all year in each country. In particular, the desired result is

LOC	_TYPE_	_FREQ_	final_amh
ARG	1	    3	    0.0001114846
AUS	1	    3	    0.0000220928
AUT	1	    3	    0.0002321934
BEL	1	    3	    0.0002852182

The code I am using is very simple

proc means data=agg_amihud_vw noprint nway;
	class LOC;
	var agg_amh_vw;
	output out=final_amihud mean=final_amh;
run;

However, I am not sure if we can use PROC SQL to do the same thing? I am trying to write the code, I know the code below is wrong but is there any code similar to this one and can be applied to get my desired result above?

proc sql;
	select sum(agg_amh_vw)/ count(agg_amh_vw) as value format 32.12
	by LOC /* I know this line is wrong*/
	from agg_amihud_vw;
	
quit;

Many thanks and warmest regards!

Re: How to use "BY" or the like in PROC SQL ?

Shmuel — Sun, 07 Mar 2021 08:46:04 GMT

Try next sql code:

proc sql;
	select sum(agg_amh_vw)/ count(agg_amh_vw) as value format 32.12
	from agg_amihud_vw
	group by LOC;
quit;

Re: How to use "BY" or the like in PROC SQL ?

Tom — Sun, 07 Mar 2021 14:44:52 GMT

You are looking for the GROUP BY feature of SQL. Make sure to include the grouping variable(s) in the select list.

Also in SAS you can use the MEAN() aggregate function directly. Other implementations of SQL might use a different name for that function, like AVERAGE().

proc sql;
select loc
     , mean(agg_amh_vw) as final_amh
  from agg_amihud_vw
  group by loc
;
quit;

Re: How to use "BY" or the like in PROC SQL ?

Phil_NZ — Sun, 07 Mar 2021 20:55:21 GMT

Hi @Tom

Thank you for your suggested code, yay, the line "select LOC" matters.

I adjust a little bit the code to make the result totally align with my code using proc means

proc sql;
	create table agg_amihud_vw as 
	select LOC
		,mean(agg_amh_vw) as final_amh
	  from agg_amihud_vw
	  group by LOC
	;
run;

armest regards.