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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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