DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
N/A
Posts: 0
Accepted Solution

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

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;

Accepted Solutions
Solution
‎03-01-2017 10:17 AM
Frequent Contributor
Frequent Contributor
Posts: 76

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

[ Edited ]

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


All Replies
Solution
‎03-01-2017 10:17 AM
Frequent Contributor
Frequent Contributor
Posts: 76

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

[ Edited ]

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;

N/A
Posts: 0

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

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).
N/A
Posts: 0

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

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
Contributor
Posts: 20

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

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 

New User
Posts: 1

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

PROC SQL;

SELECT * FROM mydata (OBS=10);

QUIT;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 24604 views
  • 1 like
  • 4 in conversation