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!
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
Option obs=1000;
But....if you sort a data set without specifying an OUT= data this will destroy the original data.
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;
So when I applied that, I only got a single record back.
Include your code and log.
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
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
Perfect! This was very helpful Thank you.
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.
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.