topic Re: How to select first few records to test the Proc sql; in SAS Programming

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

deleted_user — Wed, 19 Mar 2008 19:00:51 GMT

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;

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

LAP — Wed, 01 Mar 2017 15:17:43 GMT

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;

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

deleted_user — Wed, 19 Mar 2008 21:03:28 GMT

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

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

deleted_user — Sat, 05 Apr 2008 14:55:50 GMT

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

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

srinidelite — Mon, 03 Apr 2017 10:16:33 GMT

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

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

SimonJF — Tue, 04 Apr 2017 02:31:22 GMT

PROC SQL;

SELECT * FROM mydata (OBS=10);

QUIT;