topic Re: remerging summary statistics with raw data in SAS Programming

remerging summary statistics with raw data

Ronein — Thu, 26 Sep 2019 05:18:59 GMT

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.*/

Re: remerging summary statistics with raw data

PeterClemmensen — Thu, 26 Sep 2019 05:21:12 GMT

Why not simply

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

Re: remerging summary statistics with raw data

DaveStar — Thu, 26 Sep 2019 05:43:29 GMT

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!

Re: remerging summary statistics with raw data

DaveStar — Thu, 26 Sep 2019 05:50:43 GMT

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

Re: remerging summary statistics with raw data

s_lassen — Thu, 26 Sep 2019 13:03:52 GMT

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!

Re: remerging summary statistics with raw data

Ronein — Thu, 26 Sep 2019 18:27:30 GMT

Thank you so much!