SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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
PeterClemmensen
Tourmaline | Level 20

Why not simply

 

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

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

Why not simply

 

proc sql;
    create table tbl as
    select *, 
           sum(sales) as total_sales,
           count(*) as Total_count
    from sashelp.shoes;
quit;
DaveStar
Obsidian | Level 7
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
Obsidian | Level 7
Sorry. I think you are right and what you wrote is remerging .Dave
s_lassen
Meteorite | Level 14

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
Meteorite | Level 14

Thank you so much!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 974 views
  • 2 likes
  • 4 in conversation