topic Select-Into-From (Proc SQL) in SAS Procedures

Select-Into-From (Proc SQL)

GVeers — Tue, 09 Nov 2010 19:32:46 GMT

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.

Re: Select-Into-From (Proc SQL)

RickM — Tue, 09 Nov 2010 19:41:24 GMT

I think you want

Proc SQL;

CREATE TABLE AS
SELECT
FROM
GROUP BY

quit;

Check the documentation for specifics.

Re: Select-Into-From (Proc SQL)

GVeers — Tue, 09 Nov 2010 19:52:20 GMT

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.)

Re: Select-Into-From (Proc SQL)

Peter_C — Wed, 10 Nov 2010 17:47:29 GMT

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

Re: Select-Into-From (Proc SQL)

GVeers — Wed, 10 Nov 2010 20:35:48 GMT

Pretty cool, Peter.C. I will keep that in mind next time.