topic Re: Proc Freq Out of memory issue in SAS Programming

Proc Freq Out of memory issue

PravinMishra — Mon, 06 Oct 2014 22:48:08 GMT

Hi,

I am facing issue with proc freq .request you to please help me out.

1.while running Proc freq on 57millions records.Its throwing an error of memory issue. I am not creating output in HTML / ODS. output is created in sasdata set and only generating nlevels (distinct value)Note that I can't use proc sql as there is some issue with distinct cout. How to mitigate memory issue.

2.Can I stop count and cummulative percent calculation.Only I need distinct value?

Thanks

Re: Proc Freq Out of memory issue

ballardw — Mon, 06 Oct 2014 22:53:39 GMT

Post the code you're using so far.

Re: Proc Freq Out of memory issue

PravinMishra — Mon, 06 Oct 2014 23:02:34 GMT

Hi,

Please find the code which I am using to pull the nlevels of records. variable are both in character and numeric.

proc freq data=table_name (keep=col_name) noprint nlevels;

table col_name/out=freqout(keep=col_name) norow nocol ;

run;

I am using norow nocol to avoid count and cum percent getting calculated but it's not helping me out.

Re: Proc Freq Out of memory issue

Reeza — Mon, 06 Oct 2014 23:12:57 GMT

57 million records is a lot and may take a while but not run out of memory. Have you tried the memsize option? Are you running on a server or a desktop?

Can you sort the data set without running out of memory?

Re: Proc Freq Out of memory issue

ballardw — Mon, 06 Oct 2014 23:40:24 GMT

You have a couple of syntax issues:

the output dataset for a single variable would have count and percent it would not have levels. NOROW and NOCOL suppress percentages of a cross tab, so they are doing nothing with your data, use NOCOUNT and NOPERCENT.

Levels information is displayed in listing or ods output. If you want the result in a dataset then you use ODS to get that.

Drop the nlevels and see if that works.

Then run a second proc freq on your output data set:

proc freq data=freqout;

tables count;

run;

the total CUMULATIVE frequency will be how many original unique values were in the data set.

I suspect you may have had similar syntax errors in your SQL approach.

Proc sql;

select count(*)

from (select distinct col_name from table_name);

quit;

Re: Proc Freq Out of memory issue

Reeza — Tue, 07 Oct 2014 00:46:25 GMT

The output wouldn't have count or percent, only the distinct values and the Nlevels won't do anything besides take up processing time, removing it does speed it up significantly as well.

Depends on what the OP wants in the final results.

Re: Proc Freq Out of memory issue

Kurt_Bremser — Tue, 07 Oct 2014 08:06:00 GMT

proc sort data=table_name (keep=col_name) out=temp1 nodupkey;

by col_name;

run;

/* work.temp1 has one line per distinct value */

/* get the dataset size from the table metadata, quicker than a count(*) */

data freqout (keep=col_name);

set sashelp.vtable (rename=(nobs=col_name) where=(libname='WORK' and memname='TEMP1'))

run;

Re: Proc Freq Out of memory issue

Ksharp — Tue, 07 Oct 2014 14:40:26 GMT

Give some sample data and the output to explain your question more .

and one more important thing is if your dataset have been sorted ?

Xia Keshan

Re: Proc Freq Out of memory issue

PravinMishra — Tue, 07 Oct 2014 15:17:01 GMT

Hi ,

I have used the same syntax for proc sql but due to precision the output are different in proc freq vs proc sql distinct count.I can't use proc sql.

I understand that NOROW and NOCOL is not going to help me.I was just trying to stop calcualtion happening in cumalative and count.

I don't think NOCOUNT options are there in proc freq.

Thanks for your suggestion using ODS.

Re: Proc Freq Out of memory issue

PravinMishra — Tue, 07 Oct 2014 15:28:06 GMT

Hi Kurt,

Thanks for your response and suggestions.

I have already tried proc sort with nodupkey and proc sql.but count are not matching with one of my report.

Due to which I can't use it.

Thanks

Re: Proc Freq Out of memory issue

Kurt_Bremser — Wed, 08 Oct 2014 06:44:22 GMT

Then your report is obviously broken.

Re: Proc Freq Out of memory issue

jakarman — Wed, 08 Oct 2014 09:00:42 GMT

I tested with a 21M observations faked dataset on UE.

There is no problem with that. If you have a lot of different values all what is counted needs to be in memory.

Sorting/ordering on the variable and than doing count with by processing will limit memory usage.

27M is still small unless you have big/long variables.

What are you system limits resources versions and environment?

44 data carsm (drop=i);

45 set sashelp.cars;

46 do i=1 to 50000; output;

47 end;

48 run;

NOTE: There were 428 observations read from the data set SASHELP.CARS.

NOTE: The data set WORK.CARSM has 21400000 observations and 15 variables.

NOTE: DATA statement used (Total process time):

real time 24.44 seconds

cpu time 9.43 seconds

49 options fullstimer;

50 proc freq data=carsm ;

51 table make / noprint norow nocol out=cart ;

52 run;

NOTE: There were 21400000 observations read from the data set WORK.CARSM.

NOTE: The data set WORK.CART has 38 observations and 3 variables.

NOTE: PROCEDURE FREQ used (Total process time):

real time 3.02 seconds

user cpu time 1.99 seconds

system cpu time 0.48 seconds

memory 677.43k

OS Memory 23188.00k

Timestamp 08-10-2014 05:02:12 AM

Step Count 6 Switch Count 34

Page Faults 3

Page Reclaims 242

Page Swaps 0

Voluntary Context Switches 94

Involuntary Context Switches 61

Block Input Operations 672

Block Output Operations 280

Re: Proc Freq Out of memory issue

Reeza — Wed, 08 Oct 2014 13:39:12 GMT

50827 - When you use the COUNT(Distinct X) function in the SQL procedure, incorrect counts might be returned

How was your report calculated?

You never mentioned if you were running on a server or desktop?

Re: Proc Freq Out of memory issue

Kurt_Bremser — Wed, 08 Oct 2014 13:51:25 GMT

Ha! Just (right this moment) had one of these over here.

Another "why I don't use SQL in SAS, unless absolutely necessary".

The proc SQL in question took 46 minutes real time, while the data step solution took 6 seconds(!!!). And the SQL dropped 1 observation one of the counts on top of that.

Re: Proc Freq Out of memory issue

PravinMishra — Wed, 08 Oct 2014 15:42:38 GMT

Hi Reeza,

Apologize for not mentioning it.I am running it on server.

Thankss

Re: Proc Freq Out of memory issue

Reeza — Wed, 08 Oct 2014 15:57:53 GMT

Post your full code/log with error, on a server you should definitely not be running out of memory.

If you still are try something like the following. It's brute force and I would never use it in production:

*trim data set first;

data first_pass;

set have;

by col_name notsorted;

if first.col_name;

run;

*then run a proc freq on it;

proc freq data=first_pass nlevels;

table col_name;

run;

Re: Proc Freq Out of memory issue

PravinMishra — Tue, 04 Nov 2014 21:31:58 GMT

Hi Jaap,

Below is the error.

ERROR: Not enough memory for all variables. Variable RAR_ENTERED_ACCT removed after 3470836 levels.

ERROR: The SAS System stopped processing this step because of insufficient memory.

Below is the Code which I am running

proc freq data=SRS_AUTH_201302 noprint;

&num_freq;

run;

&num_freq is a macro variable having last of 45 variable with proper syntax.

Re: Proc Freq Out of memory issue

Reeza — Wed, 05 Nov 2014 00:21:27 GMT

You should show what &num_freq looks like.

If it's something like

table var1*var2*var3*...*var45/out=want

Then you can try replacing that with a Proc SQL statement instead or partitioning the data set and running the code multiple times.

Re: Proc Freq Out of memory issue

jakarman — Wed, 05 Nov 2014 06:02:26 GMT

This paragraph Base SAS(R) 9.4 Procedures Guide: Statistical Procedures, Second Edition explains the resource usage in proc print.

Giving a variable list all possible values must fit into memory. That RAR_ENTERED_ACCT has a lot of them, wondering how that printed list would be.

Proc options will show all settings including the memory but you can limit that to group is memory. Base SAS(R) 9.4 Procedures Guide, Third Edition

Take all your variables in that list do those calculations and it will most likely showing you you are running out of memory

Normally with big datasets is doesn't make sense to print all variables in a dataset as there are some rather unique ones like accountnumbers or record-id.

When there is a measurement a classification (using format) can be very useful.

Re: Proc Freq Out of memory issue

Kurt_Bremser — Wed, 05 Nov 2014 07:02:11 GMT

Variable RAR_ENTERED_ACCT removed after 3470836 levels.

This simply means that at the time of the error, 3470836 distinct values for RAR_ENTERED_ACCT had been encountered, which signals that this variable is not one for which freq makes ANY sense. Or do you like to read sheets with > 3 million lines?

Inspect the data first visually to determine which variables can sensibly be counted for distinct values!!