BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dogo23
Quartz | Level 8

Hello,

 

Is there a simple was to do a Limit function in proc sql like I would do in MySQL? I just want to limit the number of row returned (like first 100 or a random assortment equaling 100).

 

I would have just tagged this at the end of a statement (see below), but it did not work in SAS EG. When I google it, I see forums with all of these elaborate methods to get the results but I want something simple if it exists.

 

limit 100; 

quit;

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

As you are doing inner join with few records, which is creating less number of records from table1 (obs = 100) a. 

 

Please remove obs = 100 and then do

proc sql outobs = 100;
create table city_residents 

View solution in original post

7 REPLIES 7
Reeza
Super User

Option obs=1000;

 

But....if you sort a data set without specifying an OUT= data this will destroy the original data.

SASKiwi
PROC Star

THe INOBS and OUTOBS options are probably what you need. You can also OBS = 100 on individual SAS datasets to limit the number of rows read:

proc sql;
  create table want
  as select *
  from have (obs = 100)
  ;
quit;
Dogo23
Quartz | Level 8

So when I applied that, I only got a single record back.

Reeza
Super User

Include your code and log. 

 

Dogo23
Quartz | Level 8

proc sql;
create table city_residents as
select
a.salutation_title,
a.first_name,
a.last_name,
a.suffix,
a.address_line1,
a.address_line2,
a.city,
a.state_province,
a.zip_code

from table1 (obs = 100) a
inner join
table2 b
on a.zip_id = b.mc_zip_id
inner join
table3 c
on a.individual_num = c.individual_num
where a.pref_language_code = 'ENG'
and b.MSA_NAME like'%Nashville%'
and a.address_line1 <> ' '
and a.MAILABLE = 'Y'
and a.pref_language_code = 'ENG'
group by 1;
quit;

 

 

 

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 proc sql;
24 create table locals as
25 select
26 a.salutation_title,
27 a.first_name,
28 a.last_name,
29 a.suffix,
30 a.address_line1,
31 a.address_line2,
32 a.city,
33 a.state_province,
34 a.zip_code
35 from table1 (obs = 100) a
36 inner join
37 table2 b
38 on a.mc_zip_id = b.mc_zip_id
39 inner join
40 table c
41 on a.INDIVIDUAL_NUM = c.INDIVIDUAL_NUM
42 where a.pref_language_code = 'ENG'
43 and b.MSA_NAME like'%Nashville%'
44 and a.address_line1 <> ' '
45 and a.MAILABLE = 'Y'
46 and a.pref_language_code = 'ENG'
47 or a.tier_name in ('Member', 'Discoverist') and c.qualifying_stays_last_12m >=1)
48 group by 1;
NOTE: The "<>" operator is interpreted as "not equals".
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING
clause of the associated table-expression referenced a summary function.
NOTE: Table WORK.LOCALS created, with 16 rows and 9 columns.

50 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 4.84 seconds

kiranv_
Rhodochrosite | Level 12

As you are doing inner join with few records, which is creating less number of records from table1 (obs = 100) a. 

 

Please remove obs = 100 and then do

proc sql outobs = 100;
create table city_residents 

Dogo23
Quartz | Level 8

Perfect! This was very helpful Thank you.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 7 replies
  • 20640 views
  • 1 like
  • 4 in conversation