Optimize IT resource capacity and performance with SAS

Limit number of observations returned from SQL Server connection

Reply
Contributor
Posts: 24

Limit number of observations returned from SQL Server connection

Morning all,

Is there a way to limit the number of observations a user can return from a SQL Server table? Perhaps a setting SMC or an option in one of the config files?

We are running SAS 9.2 on linux.

Thanks in advance

Jamie

Super User
Posts: 981

Re: Limit number of observations returned from SQL Server connection

Maybe this article could be of your interest:

36112 - Managing large SAS® data sets that exceed the maximum number of observations

I don't know if your users are making use of Enterprise Guide or SAS Base. My first guess would go to the first option.

In Enterprise Guide (I am not sure in 9.2 right now) in Tool-Options-Data-Performance, you should be able to set the number of rows to display.

In any code, you can also make use of the OBS paramenter: SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition and

Use the OBS and FIRSTOBS Options to Save Time While Testing - sasCommunity

If there is any specific for SQL Server, sorry, I don;t know. But if any, you should be able to include in the library definition with: library-properties-Options-Advanced Options- Other options.

Contributor
Posts: 24

Re: Limit number of observations returned from SQL Server connection

Hi There,

Thanks for your reply. What i'm looking to do is restrict the users, so if someone had to code, say:

data test;

set mylib.data(obs=500000);

run;

then have some sort of configuration or setting to over-ride that to 10,000 for example.

Thanks,

Jamie

Grand Advisor
Posts: 9,320

Re: Limit number of observations returned from SQL Server connection

Not sure if this could work. Try these two ways.(read 10 records one time)

libname x sqlsrv .........  readbuff=10;

proc sql inobs=10;

Xia Keshan

Esteemed Advisor
Posts: 5,954

Re: Limit number of observations returned from SQL Server connection

Since the OBS= option(s) can be set anywhere in the SAS program, they are no valid tool for reliably keeping users from overloading your SQL server with big requests (I assume this is what you want).

Check if it is possible to set such a limit for certain users in the SQL server. For SAS I don't see one.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Post a Question
Discussion Stats
  • 4 replies
  • 1410 views
  • 0 likes
  • 4 in conversation