PROC FREQ with SAS In database Processing - Please help

[aruku]

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.

[MohammadFayaz]

Hi,

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

[aruku]

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

[Reeza]

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]

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]

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

Many thanks

G

[aruku]

I am using SAS Access to Teradata engine. Thanks.

[SASKiwi]

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]

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. ?