SAS: DATA STEP vs. PROC SQL

Question

Does anybody know why proc sql can not be used in DATA STEP in SAS/

For example,

DATA test;
set lib.table;
  PROC SQL;
   ...
  QUIT;
  ...some operators...
RUN;

After PROC SQL..RUN; on operators are not processed.

Thanks for explanation.

Answer 1

Because like all PROC's - it operates on/produces a dataset - it doesn't make sense inside a data step. You can use the FROM clause of SQL to specify an input (similar to the datastep set <setname>), and you can create a new dataset as output by using SQL's CREATE TABLE (similar to data <setname>).

proc sql;
    create table lib.new_table as select * from lib.table;
run;

There's nothing to stop you mixing the functionality of one with the other using views where appropriate, eg:

data lib.new_set_view / view=lib.new_set_view;
    set lib.some_set;
    /* do normal operations and output's */
run;

proc sql;
    create table lib.other_set as select * from lib.new_set_view where something='whatever';
quit;

Answer 2

Jon's entirely correct, but I will add that in 9.4, dosubl does allow this functionality in a way (though it's generally not a great idea, due to speed issues):

%macro procsql(sex);
%global mycount;
proc sql noprint;
select count(1) into :mycount from sashelp.class where sex="&sex.";
quit;
%mend procsql;

data addcount;
set sashelp.class;
rc=dosubl('%procsql('||sex||')');
count = input(symget("mycount"),best12.);
run;