BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
I remember the fetch statement can be used in proc sql to select first few rows of table to test the code. I use following code but return an error message.

proc sql;
create table ab.temp as
select a.policy_nbr, a.tx_date, b.val
from ac.W_TC_TRANSS_F a, ac.w_lov_d b
fetch first 100 rows only;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
LAP
Quartz | Level 8 LAP
Quartz | Level 8

I'm not familiar with Fetch but you can use inobs= or outobs= option on the PROC SQL statement.

INOBS= restricts the number of rows from any of the input sources, so in your example if inobs=10 then the maximum number of rows used for input would be 10 for ac.W_TC_TRANSS_F and 10 for ac.w_lov_d b. Since you don't use a where clause in the example, you would return 100 rows.  If your library is a database table, SAS will push the correct instruction to the database to limit the amount of data it processes.

OUTOBS= restricts the number of rows returned from your query, so outobs = 10 would return only the first 10 rows.  The full data is evaluated in the query, but the results set is limited in size, which might save on I/O from a database back to SAS.  Hope this helps.

Linda


> I remember the fetch statement can be used in proc
> sql to select first few rows of table to test the
> code. I use following code but return an error
> message.
>
> proc sql;
> create table ab.temp as
> select a.policy_nbr, a.tx_date, b.val
> from ac.W_TC_TRANSS_F a, ac.w_lov_d b
> fetch first 100 rows only;
> run;

View solution in original post

5 REPLIES 5
LAP
Quartz | Level 8 LAP
Quartz | Level 8

I'm not familiar with Fetch but you can use inobs= or outobs= option on the PROC SQL statement.

INOBS= restricts the number of rows from any of the input sources, so in your example if inobs=10 then the maximum number of rows used for input would be 10 for ac.W_TC_TRANSS_F and 10 for ac.w_lov_d b. Since you don't use a where clause in the example, you would return 100 rows.  If your library is a database table, SAS will push the correct instruction to the database to limit the amount of data it processes.

OUTOBS= restricts the number of rows returned from your query, so outobs = 10 would return only the first 10 rows.  The full data is evaluated in the query, but the results set is limited in size, which might save on I/O from a database back to SAS.  Hope this helps.

Linda


> I remember the fetch statement can be used in proc
> sql to select first few rows of table to test the
> code. I use following code but return an error
> message.
>
> proc sql;
> create table ab.temp as
> select a.policy_nbr, a.tx_date, b.val
> from ac.W_TC_TRANSS_F a, ac.w_lov_d b
> fetch first 100 rows only;
> run;

deleted_user
Not applicable
The only thing I could find that is remotely related is the use of FETCH in SQL Server and some other RDMS's to retrieve a result set into a cursor.

What I could find in SAS's documentation was for FETCH to simply either retrieve the next observation from a SAS dataset that was "OPEN"ed or to retrieve a specific observation, by number (random access).
deleted_user
Not applicable
There are 2 waya of dowing this

1. Before giving proc sql command , you can specify options obs =10;

or

2. Inside proc sql, you can give rownum <11
srinidelite
Obsidian | Level 7

When i try with Rownum ..i get the follwing error

ERROR: The following columns were not found in the contributing tables: rownum.

 

With OBS  ,,, no effect 

SimonJF
Calcite | Level 5

PROC SQL;

SELECT * FROM mydata (OBS=10);

QUIT;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 359095 views
  • 18 likes
  • 4 in conversation