Help using Base SAS procedures

PROC FREQ with SAS In database Processing - Please help

Reply
Occasional Contributor
Posts: 15

PROC FREQ with SAS In database Processing - Please help

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.

Contributor
Posts: 62

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

Hi,

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

Occasional Contributor
Posts: 15

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

Posted in reply to MohammadFayaz

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

Super User
Posts: 19,878

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

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.

Occasional Contributor
Posts: 15

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

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.

Contributor
Posts: 41

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

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

Many thanks

G

Occasional Contributor
Posts: 15

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

Posted in reply to gra_in_aus

I am using SAS Access to Teradata engine. Thanks.

Super User
Posts: 3,261

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

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

New Contributor
Posts: 2

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

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

Ask a Question
Discussion stats
  • 8 replies
  • 441 views
  • 0 likes
  • 6 in conversation