proc sql view vs table

[qinghe]

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

[andreas_lds]

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.

[Quentin]

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;

[Tom]

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

[dxiao2017]

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.