topic Re: proc sql view vs table in SAS Programming

proc sql view vs table

qinghe — Wed, 17 Sep 2025 01:01:28 GMT

Hi,

I tried the following codes:

proc sql outobs=8;
create view A as
select * from sashelp.class;
quit;

and
proc sql inobs=8;
create view B as

select * from sashelp.class;
quit;

For both of them the condition outobs=8 or inobs=8 does not work. If I change view into table, either of them works. Why?

Regards,

Qinghe

Re: proc sql view vs table

andreas_lds — Wed, 17 Sep 2025 05:27:10 GMT

First thought: looks like a bug.

Second thought: it is logical that the option is ignored.

The options inobs and outobs are processed during the execution of proc sql, but are not included in the code of the created views. The contents of a view is created on accessing the view. Should be noted in the documentation, though.

Re: proc sql view vs table

Quentin — Wed, 17 Sep 2025 13:10:11 GMT

Interesting, I suppose it's sort of like running:

options obs=10 ;
proc sql ;
create view A as
select * from sashelp.class;
quit;

The view is defined based on the CREATE VIEW statement, it doesn't know about system options in effect, or even options on the PROC SQL statement, apparently.

A workaround is to add an obs= option to the dataset listed on the FROM clause:

proc sql ;
create view A as
select * from sashelp.class(obs=8);
quit;

Re: proc sql view vs table

Tom — Wed, 17 Sep 2025 20:02:18 GMT

Because CREATING a view definition neither inputs nor outputs any observations.

Try adding those options to the PROC SQL step that uses the view to actually access the data.

1    proc sql;
2    create view A as
3    select * from sashelp.class;
NOTE: SQL view WORK.A has been defined.
4    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.12 seconds
      cpu time            0.04 seconds


5
6    proc sql outobs=8 inobs=10 ;
7    select * from A;
NOTE: Writing HTML Body file: sashtml.htm
WARNING: Statement terminated early due to OUTOBS=8 option.
8    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.60 seconds
      cpu time            0.10 seconds


9    proc sql;
10   create view A as
11   select * from sashelp.class;
NOTE: SQL view WORK.A has been defined.
12   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


13
14   proc sql outobs=10 inobs=8 ;
15   select * from A;
WARNING: Only 8 records were read from SASHELP.CLASS due to INOBS= option.
16   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: proc sql view vs table

dxiao2017 — Thu, 18 Sep 2025 03:28:13 GMT

Hi @qinghe , I think you have the right conclusion about the inobs=; and outobs=; in proc sql step, that is, they do not work for table and views. From my experience I had the same conclusion, that is, inobs=; and outobs=; only work for the default output of proc sql step (when one does not ask proc sql to create table or view). They work in the same way that the (obs=;) in proc print step, that is, they do NOT change anything of the dataset (e.g., how many rows it has) but only determine how many rows the proc print step print out in the result window.

If one would like to select, or keep only the first several rows of a table to produce a new table, other techniques should be applied, such as use if _n_=num in a data step, or, I agree with @Quentin 's suggestion, use (obs=num) behind table name in proc sql step.

Re: proc sql view vs table

dxiao2017 — Fri, 26 Sep 2025 10:14:55 GMT

Hi today I somehow suddenly found that I had a wrong impression about outobs=num in proc sql, and found outobs=num in fact works for create table, the code and log are as follows:

proc sql outobs=3;
create table test as
select * from sashelp.class;
describe table test;
quit;

69         proc sql outobs=3;
 70         create table test as
 71         select * from sashelp.class;
 WARNING: Statement terminated early due to OUTOBS=3 option.
 NOTE: Table WORK.TEST created, with 3 rows and 5 columns.