In PROC SQL what is the difference between inobs and outobs

[Moksha]

Hi All,

        I have been trying to understand the difference between inobs and outobs, but it's not clear to me. 

I have referred to the earlier post on this forum at:

https://communities.sas.com/t5/SAS-Programming/Difference-between-in-inobs-and-outobs/m-p/419910#M10...

and executed the example given by Reeza. In this example, we run the code with outobs = 2 first and the result contains 2 records where age is 16 and 15 and after this if we run the code with inobs =2 then the result contains 2 records where age is 14 and 13.

I didn't understand why inobs result is different from outobs.

Also, I have gone through the documentation on SAS site:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0a693shdq473qn1svlwshfci9rg.htm#n0pz...

but, I am not clear on the differences between the two.

Can anyone help me to understand the differences between the two, giving some examples? 

Thanks

[JosvanderVelden]

Please have a look at this thread: https://communities.sas.com/t5/SAS-Programming/Difference-between-in-inobs-and-outobs/td-p/419910

[Moksha]

Thank you for the reply. I have already gone through this thread (I have mentioned the same post in my query), but it's not clear to me. Can you please provide few more examples explaining the differences?

[PaigeMiller]

I think the documentation is clear. I have highlighted the differences:

The INOBS= option restricts the number of rows that PROC SQL takes as input from any single source. For example, if you specify INOBS=10, then PROC SQL uses only 10 rows from any table or view that is specified in a FROM clause. If you specify INOBS=10 and join two tables without using a WHERE clause, then the resulting table (Cartesian product) contains a maximum of 100 rows. The INOBS= option is similar to the SAS system option OBS=.

The OUTOBS= option restricts the number of rows that PROC SQL displays or writes to a table. For example, if you specify OUTOBS=10 and insert values into a table by using a query, then PROC SQL inserts a maximum of 10 rows into the resulting table. OUTOBS= is similar to the SAS data set option OBS=.

[Moksha]

Thank you for the explanation. As per this, the following code with INOBS=5 should result in 5 records with age is 16, 15, 14, 13 and 12. But, it is resulting in only 2 records with age 14 and 13. I am not able to understand this.  But, the code with OUTOBS=5 gives 5 records. Hence, not able to understand why INOBS is resulting in only 2 records when it is specified that INOBS=5. Can you please clarify?

Code:

title 'InObs demo';
proc sql inobs=5;
select age, count(*)
from sashelp.class
group by age
order by age desc;
;
quit;

Output:

                                                                                         InObs demo

Age  

14	3
13	2

But, the following code with OUTOBS=5 results in 5 records with ages 16, 15, 14, 13, 12: 

Code: 

title 'OutObs demo';
proc sql outobs=5;
select age, count(*)
from sashelp.class
group by age
order by age desc;
;
quit;

Here is the result of this code:

                                                                                       OutObs demo

Age  

16	1
15	4
14	4
13	3
12	5

[Tom · Accepted solution]

Your print out shows that it did that what you asked. 

In the first one there were 3 observations with AGE=14 and 2 with AGE=13 for a total of 5 observations read IN.

In the second one it wrote out five observations.  It had to read in 17 observations to find the 5 different values of AGE that it wrote OUT.

[Moksha]

Thank you very much for the clear explanation. Now. I understand how to interpret the output from INOBS and OUTOBS.  

[Kurt_Bremser]

Just look at the first 5 observations in sashelp.class and the age values you find there.

[PaigeMiller]

---

@Moksha wrote:

Thank you for the explanation. As per this, the following code with INOBS=5 should result in 5 records with age is 16, 15, 14, 13 and 12.

I don't see how you come to this understanding based upon the words in the documentation. It is taking 5 records, regardless of age.

[Moksha]

Somehow I was not clear with INOBS. I thank everyone for clarifying it.