topic Re: In PROC SQL what is the difference between inobs and outobs in SAS Programming

In PROC SQL what is the difference between inobs and outobs

Moksha — Fri, 26 May 2023 08:10:19 GMT

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#M103290

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#n0pz0czrns2pksn1im05lnw36t98

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

Re: In PROC SQL what is the difference between inobs and outobs

JosvanderVelden — Fri, 26 May 2023 08:59:29 GMT

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

Re: In PROC SQL what is the difference between inobs and outobs

Moksha — Fri, 26 May 2023 10:10:24 GMT

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?

Re: In PROC SQL what is the difference between inobs and outobs

PaigeMiller — Fri, 26 May 2023 10:26:07 GMT

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=.

Re: In PROC SQL what is the difference between inobs and outobs

Moksha — Fri, 26 May 2023 14:36:00 GMT

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

Re: In PROC SQL what is the difference between inobs and outobs

Tom — Fri, 26 May 2023 14:56:40 GMT

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.

Re: In PROC SQL what is the difference between inobs and outobs

Kurt_Bremser — Fri, 26 May 2023 15:07:17 GMT

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

Re: In PROC SQL what is the difference between inobs and outobs

Moksha — Fri, 26 May 2023 18:04:59 GMT

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

Re: In PROC SQL what is the difference between inobs and outobs

PaigeMiller — Fri, 26 May 2023 18:07:14 GMT

@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.

Re: In PROC SQL what is the difference between inobs and outobs

Moksha — Fri, 26 May 2023 18:12:50 GMT

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