BookmarkSubscribeRSS Feed
aruku
Calcite | Level 5

Hello,

We need to calculate percentiles at different dimensions of large amount of data (at 8 billion records). As we don't have PROC UNIVARIATE in SAS In Database support - thought of using proc freq to calculate cumulative frequecies and use that to calculate the percentiles.

I am able to get the distribution for a specific subset but not for all the data at one shot as it is giving me I/O error.

Can anyone please assist if there is any other way to calculate 99.9 percentiles or use PROC FREQ efficiently.

We are not transfering the data and it is huge.

Thanks in advance.

8 REPLIES 8
MohammadFayaz
Calcite | Level 5

Hi,

Have you passed "SAS Programming 3 :Advanced Techniques and Efficiencies"?

aruku
Calcite | Level 5

I assume you are asking if i am sas advanced certified. Yes, i am. Thanks,

Reeza
Super User

Is your data in native SAS datasets or on a server?

If its on a server you may want to consider using native SQL commands for that server instead.

8 billion is a lot of records regardless and will take a while, since percentile function requires a sort of data.

aruku
Calcite | Level 5

The data is a teradata table as am not doing any transfer of data. We tried doing this in teradata but is taking whole lot of time. Thanks.

gra_in_aus
Quartz | Level 8

Maybe a daft question... are you using the SAS/ACCESS Teradata Engine or SAS/ACCESS ODBC Engine...

Many thanks

G

aruku
Calcite | Level 5

I am using SAS Access to Teradata engine. Thanks.

SASKiwi
PROC Star

Have you confirmed what Teradata is doing by configuring these options:

OPTIONS sqlgeneration=dbms;

OPTIONS msglevel=i;

OPTIONS sastrace=',,,d' sastraceloc=saslog nostsuffix;

Also check out this:

http://support.sas.com/resources/papers/proceedings10/300-2010.pdf

Himanshu_Gupta
Calcite | Level 5

Hi,

Can you help me out with a similar doubt. I am not being able to do in-database processing using the SQL generation. My code is only firing a select * to the RDBMS and pulling it into SAS to perform the SAS procedure rather than performing it in the DBMS itself.

Can you give some directions on this, regarding the requirements and the correct libname statement or code snippet to perform the same. ?

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1606 views
  • 0 likes
  • 6 in conversation