topic Re: PROC FREQ with SAS In database Processing - Please help in SAS Procedures

PROC FREQ with SAS In database Processing - Please help

aruku — Sun, 08 Dec 2013 10:47:57 GMT

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.

Re: PROC FREQ with SAS In database Processing - Please help

MohammadFayaz — Sun, 08 Dec 2013 11:45:00 GMT

Hi,

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

Re: PROC FREQ with SAS In database Processing - Please help

aruku — Sun, 08 Dec 2013 20:53:50 GMT

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

Re: PROC FREQ with SAS In database Processing - Please help

Reeza — Sun, 08 Dec 2013 21:01:47 GMT

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.

Re: PROC FREQ with SAS In database Processing - Please help

aruku — Sun, 08 Dec 2013 21:05:33 GMT

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.

Re: PROC FREQ with SAS In database Processing - Please help

gra_in_aus — Sun, 08 Dec 2013 21:44:49 GMT

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

Many thanks

Re: PROC FREQ with SAS In database Processing - Please help

SASKiwi — Sun, 08 Dec 2013 23:11:16 GMT

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

Re: PROC FREQ with SAS In database Processing - Please help

aruku — Sun, 08 Dec 2013 23:37:58 GMT

I am using SAS Access to Teradata engine. Thanks.

Re: PROC FREQ with SAS In database Processing - Please help

Himanshu_Gupta — Mon, 13 Oct 2014 09:39:44 GMT

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