How to select first few records to test the Proc sql;

[deleted_user]

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;

[LAP · Accepted solution]

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]

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]

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]

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]

PROC SQL;

SELECT * FROM mydata (OBS=10);

QUIT;