remerging summary statistics with raw data

[Ronein]

Hello

I want to remerging summary statistics with raw data.

What is wrong here?

PROC SQL;
 create table tbl as
    select  a.*,
               (select sum(sales) as total_sales,
		               count(* ) as Total_count 
               from sashelp.shoes)
    from sashelp.shoes as  a
  ;
quit;

/*ERROR: A Composite expression (usually a subquery) is used incorrectly in an expression.*/

[PeterClemmensen · Accepted solution]

Why not simply

proc sql;
    create table tbl as
    select *, 
           sum(sales) as total_sales,
           count(*) as Total_count
    from sashelp.shoes;
quit;

[DaveStar]

He wants to merge summary statistics (sum of sales and count of rows) with the original raw data ( sashelp.shoes).
What you did is just calculate summary statistics without merge it back with raw data!

[DaveStar]

Sorry. I think you are right and what you wrote is remerging .Dave

[s_lassen]

The answer you got from @PeterClemmensen  is probably the solution I would suggest for this exact query. But there may be other situations where you want to use a subquery, you can do it like this:

PROC SQL;
 create table tbl as
    select  detail.*,sum.*
    from sashelp.shoes as  detail,
	               (select sum(sales) as total_sales,
		               count(* ) as Total_count 
               from sashelp.shoes) as sum

  ;
quit;

Note that as your queries get larger and more complicated, it makes thing a lot easier in the long run if you use more describing aliases than "a" and "b", in this case "detail" and "sum" seem better. I know, everybody (except yours truly) use short and cryptic aliases, but they really should not!

[Ronein]

Thank you so much!