Trouble with <alias>.* systax for selecting all columns from one table

Question

I'm doing a join here where I'm basically taking all the columns from one table with a primary key and many other columns (too many to want to type out), then left joining a second table where all columns will be aggregated against the first one's primary key.

A simplified version is as follows:

SELECT a.*, COUNT(DISTINCT b.date), SUM(b.spend)
FROM table_1 a 
LEFT JOIN table_2.b
ON a.cust_id = b.cust_id
GROUP BY a.* ;

I know that the above sytax works in SQL programs such as Teradata, but in SAS Enterprise Guide, using PROC SQL, I get the following error:

ERROR: * used in an illegal position.
ERROR: The following columns were not found in the contributing tables: a.

Basically, SAS doesn't seem to recognise the * when it is preceded by a alias.

Any suggestions? Thanks.

Answer 1

You have a period before the b alias. In addition, you cannot group by *. Try this:

SELECT a.*, COUNT(DISTINCT b.date), SUM(b.spend)
FROM table_1 a LEFT JOIN
     table_2 b
------------^
     ON a.cust_id = b.cust_id
GROUP BY a.cust_id ;

This assumes that cust_id is unique in table_1.

Answer 2

If you truly need to group by all columns in Table A, then look at the feedback option which will list all the fields in your table in the log and you can copy and paste that to the group by clause.

6    proc sql feedback;
7    create table want as
8    select *
9    from sashelp.class as a;
NOTE: Statement transforms to:

        select A.Name, A.Sex, A.Age, A.Height, A.Weight
          from SASHELP.CLASS A;

NOTE: Table WORK.WANT created, with 19 rows and 5 columns.

10   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds