topic Limit function in SAS in SAS Enterprise Guide

Limit function in SAS

Dogo23 — Tue, 12 Dec 2017 21:11:45 GMT

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!

Re: Limit function in SAS

Reeza — Tue, 12 Dec 2017 21:16:30 GMT

Option obs=1000;

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

Re: Limit function in SAS

SASKiwi — Tue, 12 Dec 2017 21:17:26 GMT

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;

Re: Limit function in SAS

Dogo23 — Tue, 12 Dec 2017 21:33:24 GMT

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

Re: Limit function in SAS

Reeza — Tue, 12 Dec 2017 21:35:18 GMT

Include your code and log.

Re: Limit function in SAS

Dogo23 — Tue, 12 Dec 2017 21:52:00 GMT

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

Re: Limit function in SAS

kiranv_ — Tue, 12 Dec 2017 22:12:24 GMT

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

Re: Limit function in SAS

Dogo23 — Tue, 12 Dec 2017 22:17:05 GMT

Perfect! This was very helpful Thank you.