Extract data using SQL, analyse and write back to SQL table

Question

I am a newbie to SAS Base, and I am struggling to create a simple program that extracts data from a table on my database, runs e.g. PROC MEANS, and writes the data back to the table.

I know how to use PROC SQL (read and update tables) and PROC MEANS, but I can't figure out how to combine the steps.

PROC SQL;
SELECT make,model,type,invoice,horsepower
FROM 
SASHELP.CARS
;
QUIT;
PROC Means;
RUN;

What I want to accomplish is create an additional column in the dataset with e.g. the mean of the horsepower.. and in the end I want to write that computed column to the table on the database.

Edit What I was looking for is this:

PROC SQL;
create table want as 
select make,model,type,invoice,horsepower
, mean(horsepower) as mean_horsepower
from sashelp.cars
;
QUIT;
PROC MEANS DATA=want;
RUN;

Show the steps you tried. Are you just asking how to have PROC MEANS write out the results to a dataset? If so look at the OUTPUT statement. — Tom, Nov 27 '16 at 15:57

score 0 · Accepted Answer · answered Nov 27 '16 at 18:15

0

SAS makes this very easy to do with SQL since it will automatically remerge summary statistics back to detailed records.

create table want as 
  select make,model,type,invoice,horsepower
       , mean(horsepower) as mean_horsepower
  from sashelp.cars
;

Or using normal SAS code.

proc means data=sashelp.cars nway noprint ;
  var horsepower ;
  output out=mean_horsepower mean=mean_horsepower ;
run;
data want ;
  set sashelp.cars ;
  if _n_=1 then set mean_horsepower (keep=mean_horsepower);
run;

answered Nov 27 '16 at 18:15

Tom

47,574
2
16
29

Thanks, Tom. So it's not possible to pass the results of an SQL query directly to a PROC statement? Or would I need to "create table A" and then do a data step where I do data B; set A? – hgaronfolo Nov 27 '16 at 18:41
Create a table. If you need to save space then create a view and access the view instead. – Tom Nov 27 '16 at 19:38
No, it's not possible to pass the results of query to a proc statement, you do this by creating a table and passing the table to the proc. When you do this, you should always reference the table in the DATA = portion of the PROC statement. – Reeza Nov 28 '16 at 01:23
Thanks for your help. I really appreciate it. I am still a bit confused though.. Should I create the SAS table from the PROC SQL like this? create table testA as select make,model,type,invoice,horsepower , mean(horsepower) as mean_horsepower from sashelp.cars ; data testB ; set tableA ; run; – hgaronfolo Nov 28 '16 at 07:24
Your last step is just making another copy of the same data. Why would you do that? – Tom Nov 28 '16 at 12:38

Extract data using SQL, analyse and write back to SQL table

1 Answers1