topic Re: Base SAS word Count with millions of rows of word in SAS Programming

Base SAS word Count with millions of rows of word

Venkat4 — Fri, 13 Nov 2020 17:12:33 GMT

This is a challenge for me especially with insufficient memory running proc freq or proc sql counts. I split the text fields into individual words and just have one column called "word". It had 140mil words, I split the data into three datasets as 50+50+40 mil. But still I can't run proc freq even using order=freq option.

Code is like below...

proc freq data=inputdata order=freq;

tables word / noprint nocum nopercent out=outputdata;

run;

I get the below...

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

NOTE: There were 3347033 observations read from the data set inputdata.

I wanted to get counts of each word and sort them in descending order.

I will be fine even having top 500 words with their counts or top 100 words with their frequency. I tried nocum nopercent etc., to avoid larger dataset, but still I am getting lot of errors related to memory issues. I even try sending into out dataset but similar memory issues show up. Please suggest if I can use PROC IML or other effective procedures that saves memory.

Re: Base SAS word Count with millions of rows of word

ballardw — Fri, 13 Nov 2020 17:16:18 GMT

Add the option NOPRINT to the proc freq statement and see if that helps.

Most of the "memory" used was likely trying to create a results table with millions of rows.

Re: Base SAS word Count with millions of rows of word

Tom — Fri, 13 Nov 2020 17:20:03 GMT

If you just want to count how many times each value appears (and don't need to generate an statistics) then there is no need to use PROC FREQ. You could count with a simple SQL statement.

proc sql;
create table outputdata as
  select word,count(*) as count
  from inputdata 
  group by word
;
quit;

Re: Base SAS word Count with millions of rows of word

mkeintz — Fri, 13 Nov 2020 17:23:54 GMT

If @ballardw 's suggestion doesn't work, then you could sort the dataset by word - then create a dataset with the word value and word frequency, using a data step or a proc using a "by word" statement. You then would have a dataset with two vars: word and word_freq:

proc sort data=inputdata out=need;
  by word;
run;

data outputdata;
  do word_freq=1 by 1 until (last.word);
    set need;
    by word;
  end;
run;

It might take a while to do the sort, but it is a simple program.

Re: Base SAS word Count with millions of rows of word

Venkat4 — Fri, 13 Nov 2020 17:36:45 GMT

Thanks, even with the noprint option I got memory issues....and it stopped right after 5mil obs

ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 5242529 observations read from the data set xxxxx

Re: Base SAS word Count with millions of rows of word

Venkat4 — Fri, 13 Nov 2020 17:40:10 GMT

Thanks, this is what I tried in the beginning. It stops right away due to memory issues. I am using the grid and they have 500GB limit for each user, this is automatically shuts down because it is taking more than 500GB - just the dataset itself is only 2GB. I think it is using some kind of intermittent table for calculations and that is taking too much space even using word or permanent library.

Re: Base SAS word Count with millions of rows of word

Tom — Fri, 13 Nov 2020 17:40:41 GMT

@Venkat4 wrote:

Thanks, even with the noprint option I got memory issues....and it stopped right after 5mil obs

ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 5242529 observations read from the data set xxxxx

I am sure PROC FREQ is trying to build some type of table of all of the values it finds in the dataset (probably a hash table). So if you have 5 million different values of WORD then it needs at least 5 million * (length(word) +8 bytes for the count variable) bytes of memory. If WORD is very long that quickly works out to a lot of memory.

Re: Base SAS word Count with millions of rows of word

ballardw — Fri, 13 Nov 2020 17:56:30 GMT

@Venkat4 wrote:

Thanks, even with the noprint option I got memory issues....and it stopped right after 5mil obs

ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 5242529 observations read from the data set xxxxx

Which version of SAS are you running? If you are connecting to a server you may be running into memory limits, i.e. disk space, because of your admin set limits.

Re: Base SAS word Count with millions of rows of word

mkeintz — Fri, 13 Nov 2020 18:27:03 GMT

@Venkat4 wrote:

Thanks, this is what I tried in the beginning. It stops right away due to memory issues. I am using the grid and they have 500GB limit for each user, this is automatically shuts down because it is taking more than 500GB - just the dataset itself is only 2GB. I think it is using some kind of intermittent table for calculations and that is taking too much space even using word or permanent library.

I think you are confusing memory limits (which is what your error messages are referring to) with disk storage limit (which is almost certainly the 500GB you refer to). If the SQL is actually reporting termination due to memory limits, it's not the 500GB - it's some other limit.

To find out how much memory your SAS program typically has available, run this to get a report:

proc options option=memsize;
run;

Then you can check with your sysadmin's to see the maximum amount of ram you can request when starting a sas session - and how to do it in your environment.

How long us your Re: Base SAS word Count with millions of rows of word

Kurt_Bremser — Fri, 13 Nov 2020 18:28:10 GMT

How long is your "word" variable defined, and how long is the longest value in it?

Re: How long us your Re: Base SAS word Count with millions of rows of word

Venkat4 — Fri, 13 Nov 2020 19:54:50 GMT

It assigned 10000 not sure why, I don't have any longer word. I see that could be the reason this whole thing is blowing up. Thanks.

Re: Base SAS word Count with millions of rows of word

Venkat4 — Fri, 13 Nov 2020 19:56:12 GMT

Thank you, much. This worked with the 3 way split dataset fairly quickly (15-20min each) and didn't throw any issues. I am working on combining them and getting the final counts.

Re: Base SAS word Count with millions of rows of word

Venkat4 — Fri, 13 Nov 2020 19:57:32 GMT

It is SAS 9.4 M5 running as a client-server via EG 7.15

Re: Base SAS word Count with millions of rows of word

Venkat4 — Fri, 13 Nov 2020 19:58:05 GMT

Understood, thank you.

Re: Base SAS word Count with millions of rows of word

FreelanceReinh — Fri, 13 Nov 2020 20:08:48 GMT

Hi @Venkat4,

Glad to see that mkeintz's suggestion worked for you. So, my thoughts below might be of interest for later readers with a similar problem.

@Venkat4 wrote:

(...) I split the data into three datasets as 50+50+40 mil. But still I can't run proc freq (...)

Whether you use PROC FREQ or PROC SQL or a DATA step with a hash object, splitting the input dataset should help if the subsets don't overlap. To ensure that, don't split by observation number, but by other criteria such as length(word), lowcase(first(word)), etc.

Example:

data h1 h2 h3 h4 h5;
set inputdata;
select(length(word));
  when(1,2,3)  output h1;
  when(4,5)    output h2;
  when(6,7)    output h3;
  when(8,9,10) output h4;
  otherwise    output h5;
end;
run;

(Consider using the COMPRESS=YES dataset option on h1, h2, ...)

If you're unsure about suitable cut points, you can draw a reasonably sized random sample ...

proc surveyselect data=inputdata
method=srs n=100000
seed=2718 out=samp;
run;

[edit: use a DATA step for this if you don't have SAS/STAT]

and analyze that quickly to get an idea of the distributions of lengths, first letters, etc. and even relative word frequencies. A frequency count of the combinations of the first two letters like

proc freq data=samp noprint;
format word $2.;
tables word / out=cnt;
run;

might be feasible even on the full dataset.

Re: How long us your Re: Base SAS word Count with millions of rows of word

Kurt_Bremser — Fri, 13 Nov 2020 21:04:01 GMT

Since any utility file is uncompressed (and the in-memory structures of procedures like FREQ also), this will invariably cause problems.

Check for max(length(word)), and adjust your dataset accordingly. Also consider converting all words to lowercase, to avoid differences caused by words starting a sentence. That reduces the size of the in-memory tables.

After all, in everyday language the number of words actually used is a few 1000, so this should not cause any memory problems.

The Oxford Dictionary of English contains 355000 words, so even with a word length of 100 you'd need just 40 MB (50 including a search tree) for the freq table.

Re: How long us your Re: Base SAS word Count with millions of rows of word

Ksharp — Sat, 14 Nov 2020 11:58:01 GMT

As Kurt mentioned , open the dataset and right click the column and check the length of column, I doubt its length is very too big to make this error happen.

Re: Base SAS word Count with millions of rows of word

ChrisNZ — Sun, 15 Nov 2020 04:11:23 GMT

Using SPDE should be faster than using proc sort.

And SAS is not very efficient when dealing with 10k-char strings.

Do you want to try this?

data WORDS;  * Generate sample data;
  length WORD $10000;
  do I=1 to 100e6;
    WORD=repeat( cats(int(ranuni(1)*100)), abs(int(rannor(1)*99)) );
    output;
  end;
run;
        
data _null_;  * find longest string;
  set WORDS ;
  retain LEN; 
  LEN=max(LEN,length(WORD));
  if LASTOBS then call symputx('len',LEN);
run;

%let wdir=%sysfunc(pathname(WORK));
libname W spde "&wdir" partsize=1T compress=binary;

data W.WORDS1;   * Copy data to SPDE and trim variable;
  length WORD $&len.;
proc append data=WORDS base=W.WORDS1 force;
run;

data COUNTS;  * Derive word cardinality;
  set W.WORDS1;
  by WORD;
  CNT+1;
  if last.WORD then do;
    output;
    CNT=0;
  end;
run;

This uses 35 minutes:

37         data _null_;
38           set WORDS end=LASTOBS;
39           retain LEN;
40           LEN=max(LEN,length(WORD));
41           if LASTOBS then call symputx('len',LEN);
42         run;
NOTE: DATA statement used (Total process time):
      real time 4:09.76
      user cpu time 3:53.46
      system cpu time 15.75 seconds
43         
44         data W.WORDS1;
45           length WORD $&len.;
46         proc append data=WORDS base=W.WORDS1 force;
47         run;
NOTE: PROCEDURE APPEND used (Total process time):
      real time           4:55.10
      user cpu time       7:17.37
      system cpu time     36.93 seconds
48         
49         data COUNTS;
51           set W.WORDS1;
52           by WORD;
53           CNT+1;
54           if last.WORD then do;
55             output;
56             CNT=0;
57           end;
58         run;
NOTE: DATA statement used (Total process time):
      real time           26:15.31
      user cpu time       13:07.46
      system cpu time     14:12.42

on my old server. So old I cannot use rand('uniform') ! 🙂