topic Re: Why we need both select and group by in proc sql? in SAS Programming

Why we need both select and group by in proc sql?

Phil_NZ — Sun, 28 Mar 2021 05:12:42 GMT

Hi all SAS Users,

Today I played around with the sashelp.class and I wrote a code as below to count the distinct name of students in each gender, and the code run flawlessly.

proc sql;
select sex
   ,count(distinct(name)) as no_of_name
   from sashelp.class
group by sex;
quit;

Even I wrote the code, but I am not sure why we need both select and group by within this data step? I can understand that we need group by to announce the compiler that we want to deal with the name in each sex category but what select for?

Warmest regards.

Re: Why we need both select and group by in proc sql?

Kurt_Bremser — Sun, 28 Mar 2021 05:35:09 GMT

First of all, this is not a DATA step, this is a PROC SQL step.

And the SQL statement that starts a query is SELECT. It is basic SQL syntax.

Re: Why we need both select and group by in proc sql?

japelin — Sun, 28 Mar 2021 06:22:57 GMT

I think we should learn SQL first, not SAS.

SELECT:
https://www.w3schools.com/sql/sql_select.asp

If you write the following code without gender, you will see the difference.
You will not be able to tell what the counted number corresponds to.

proc sql;
select count(distinct(name)) as no_of_name
   from sashelp.class
group by sex;
quit;