🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 09-26-2019 01:18 AM
(973 views)
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.*/
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Why not simply
proc sql;
create table tbl as
select *,
sum(sales) as total_sales,
count(*) as Total_count
from sashelp.shoes;
quit;
5 REPLIES 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Why not simply
proc sql;
create table tbl as
select *,
sum(sales) as total_sales,
count(*) as Total_count
from sashelp.shoes;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
What you did is just calculate summary statistics without merge it back with raw data!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry. I think you are right and what you wrote is remerging .Dave
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much!