BookmarkSubscribeRSS Feed
GVeers
Calcite | Level 5
In other versions of SQL (non-SAS) there is an elegant way to create a table of summarized data using

SELECT
INTO
FROM
GROUP BY

However the "into" function in Proc SQL seems quite different. It creates a macro variable rather than a new table.

Is there an elegant way to do something like this? Note that I cannot do a simple Proc Summary, as some variables need to be summed and some need to be maximized. Thank you in advance.
4 REPLIES 4
RickM
Fluorite | Level 6
I think you want

Proc SQL;

CREATE TABLE AS
SELECT
FROM
GROUP BY

quit;

Check the documentation for specifics.
GVeers
Calcite | Level 5
You are my official Savior for the Day. Thank you thank you.

(By the way, I'm sure there's a good reason for it, but it really drives me nuts that there are 50 million versions of the SQL language.)
Peter_C
Rhodochrosite | Level 12
GVeers

> Note that I cannot do a simple Proc Summary, as some
> variables need to be summed and some need to be
> maximized. Thank you in advance.

note that all you were seeking can be achieved in proc summary. You may not have noticed that most of its documentation is provided under the procedure name "proc means".
For example
proc means data= sashelp.class ; * names the data to analyse ;
class sex ; * grouping variable ;
var age height weight ; * variables for statistics ;
output out= stats /* names your output table */
min( age) = youngest
max( age)= oldest
mean(age)= average_age
sum( weight)= total_weight
max(height)= tallest ;
run ;
provides a table with 3 rows:
first an overall summary,
then a row for each value of the class variables
The results table has columns
one column for each class variable (just sex in this example)
a column (_type_) which indicates the aggregating level
a column (_freq_) indicating the number of input rows contributing
and a column for each statistic requested.

so just wanted to clarify that, although you may achieve the summarising you need in proc SQL, you might find PROC MEANS more helpful.

good luck
peterC
GVeers
Calcite | Level 5
Pretty cool, Peter.C. I will keep that in mind next time.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2013 views
  • 0 likes
  • 3 in conversation