topic Re: DATA MERGE on large datasets but SAS runs out of memory in SAS Programming

DATA MERGE on large datasets but SAS runs out of memory

Cruise — Tue, 23 Apr 2019 12:41:46 GMT

Hi Folks, I have a work space issue

I ran SAS overnight to merge Z.ALL_SITES (76.4GB) and Z.DATA_LARGE (560MB). SAS crashed "out of memory" this morning. And I cleaned H and Z folders except the datasets needed. Folder ''H" has 1.96TB for a free space. Folder Z has 490GB for a free space.

I'll appreciate any suggestions.

Thanks zillions in advance. This is being a huge roadblock. I can't move on my project because of this memory issue.

DATA H.MERGED;
MERGE
     Z.ALL_SITES  (IN=A)
     Z.DATA_LARGE (IN=B);
BY ID;
IF A AND B;
RUN;

Re: DATA MERGE on large datasets but SAS runs out of memory

Kurt_Bremser — Tue, 23 Apr 2019 12:56:35 GMT

Maxim 3: Know Your Data. Inspect your input datasets for large columns, and if those datasets are stored with compress=yes.

If you are unsure, post the output of proc contents for both datasets here.

Re: DATA MERGE on large datasets but SAS runs out of memory

PaigeMiller — Tue, 23 Apr 2019 13:13:47 GMT

@Cruise wrote:

Hi Folks, I have a work space issue

I ran SAS overnight to merge Z.ALL_SITES (76.4GB) and Z.DATA_LARGE (560MB). SAS crashed "out of memory" this morning. And I cleaned H and Z folders except the datasets needed. Folder ''H" has 1.96TB for a free space. Folder Z has 490GB for a free space.

A minor point here: running out of memory is not the same thing as not having enough free disk space. I think @Kurt_Bremser is on the right track to solving the problem.

Re: DATA MERGE on large datasets but SAS runs out of memory

Cruise — Tue, 23 Apr 2019 13:21:17 GMT

@Kurt_Bremser @PaigeMiller

Thanks a lot. Below is the proc contents of both datasets. I only need ID, VAR1 and VAR2 from the DATA_LARGE and tempted to keep the variables I only needed. However, since I can not remove original DATA_LARGE from the folder then this would just reduce the work space in the folder R.

Correct?

What do you think?

DATA R.DATA_LARGEA; SET R.DATA_LARGE(KEEP=ID VAR1 VAR2);
WHERE VAR1 NE .; 
RUN;

Re: DATA MERGE on large datasets but SAS runs out of memory

Patrick — Tue, 23 Apr 2019 13:25:13 GMT

@Cruise

The "out of memory" issue is kind of weird given the code you've posted.

The merge step would only ever load a single row at a time into memory (the PDV) and though not require much memory.

The only reason I can think of that the code you've posted could throw an "out of memory" error is if the "tables" in the Merge statement are actually Views - and it's these views which consume a lot of memory during view execution time (which would happen as part of the merge statement).

...so: If my theory applies then the first step is to change the views to tables (with option compress=yes); and eventually try to implement code for the views (now tables) which is more memory efficient.

Re: DATA MERGE on large datasets but SAS runs out of memory

Astounding — Tue, 23 Apr 2019 13:25:02 GMT

Regarding data set compression, the issue is not whether the incoming data sets are compressed or not. They are on the Z drive, and you need more space on the H drive.

If the data contains a lot of character variables, add this statement before the merge:

options compress=yes;

If it contains more numeric variables, add this instead:

options compress=binary;

Re: DATA MERGE on large datasets but SAS runs out of memory

Kurt_Bremser — Tue, 23 Apr 2019 13:32:21 GMT

You can do all of the work in one step:

data h.merged (compress=yes);
merge
  z.all_sites (in=a)
  z.data_large (
    in=b
    keep=id var1 var2
    where=(var1 ne .)
  )
;
by id;
if a and b;
run;

Please post the log from that step, if it fails. Use the {i} button for posting logs.

PS if you enable listing output in Enterprise Guide, you can copy/paste the text to a {i} window also, so you don't need to make screenshots.

It's my favorite method of posting results.

Re: DATA MERGE on large datasets but SAS runs out of memory

Ksharp — Tue, 23 Apr 2019 13:46:57 GMT

Since Z.DATA_LARGE (560MB) is very small.

Hash Table is the best way to do it .

or try PROC FORMAT .

Re: DATA MERGE on large datasets but SAS runs out of memory

Cruise — Tue, 23 Apr 2019 13:47:05 GMT

@Kurt_Bremser @Astounding

Kurt, I'm running the code you suggested. Last time it crashed after few hours when I ran it overnight.But this time I don't know how many hours this will run. I will post the log as soon as it's finished.

I'm concerned about Astounding's pointer that I might need more space in H drive. H drive is empty for a purpose to output MERGED dataset. I can't change the size of this folder easily because it's not my private computer.

Re: DATA MERGE on large datasets but SAS runs out of memory

Patrick — Tue, 23 Apr 2019 14:00:00 GMT

@Cruise

It appears @Ksharp actually looked at your Proc Contents report and calculated the required memory for the variables you're after. If that's only around 1/2 GB then you've got likely sufficient memory and the data step hash lookup could work.

Below code based on the code you've posted but of course not tested as you haven't provided sample data. Give it a go an let us know if it works.

data h.merged;
  if _n_=1 then 
    do;
      if 0 then set z.data_large(keep=(id var1 var2));
      dcl h1 (dataset:'z.data_large(keep=(id var1 var2))';
      h1.defineKey('id');
      h1.defineData('var1','var2');
      h1.defineDone();
    end;
  set z.all_sites;
  if h1.find()=0 then output;
run;

Re: DATA MERGE on large datasets but SAS runs out of memory

Patrick — Tue, 23 Apr 2019 14:02:22 GMT

@Cruise

Thanks for the "like". Please use the latest (current) cut of my code for testing as I had to fix it since the initial post.

Re: DATA MERGE on large datasets but SAS runs out of memory

Cruise — Tue, 23 Apr 2019 14:14:30 GMT

is h1 taking its h from libname h? I'm trying to customize the code to my actual directories. thanks

Re: DATA MERGE on large datasets but SAS runs out of memory

Patrick — Tue, 23 Apr 2019 14:21:42 GMT

@Cruise

And just for clarity: The code I've posted assumes that the relationship between z.all_sites to z.data_large is N:1 or 1:1.

If the relationship is 1:N and you're after left join logic then the code I've posted would require some additional looping (using the hash do_over method).

Re: DATA MERGE on large datasets but SAS runs out of memory

Patrick — Tue, 23 Apr 2019 14:25:32 GMT

h1 is the reference for the hash table the code defines (command DCL stands for DECLARE). You could use any name here. Conceptually the same like when defining a libref in a libname statement.

The libref for your source table is used within the dataset option of the Hash Declare statement.

Re: DATA MERGE on large datasets but SAS runs out of memory

Cruise — Tue, 23 Apr 2019 14:28:02 GMT

@Patrick

Hi Patrick,

This is 1:N relationship. 1 side has unique ID with no duplicates. N side is all the claim that patient had ever made in her/his entire medical history. So 1:N can actually be 1:1000 at least all the way to 1:5000 because claim data captures every drug, diagnosis or procedures of multiple chronic diseases.

What can be done in this context? like you said looping? can you extend the code for looping? if possible?

Re: DATA MERGE on large datasets but SAS runs out of memory

Patrick — Tue, 23 Apr 2019 14:51:52 GMT

@Cruise

O.K., I'll post some code. Without sample data to test I can't promise you though that it will be syntactically correct. It's getting late where I live so others might need to take over after that.

Re: DATA MERGE on large datasets but SAS runs out of memory

Cruise — Tue, 23 Apr 2019 14:32:40 GMT

@Patrick

I'll provide you a sample data asap.

Re: DATA MERGE on large datasets but SAS runs out of memory

Patrick — Tue, 23 Apr 2019 14:34:23 GMT

@Cruise

Here you go:

data h.merged;
  if _n_=1 then 
    do;
      if 0 then set z.data_large(keep=(id var1 var2));
      dcl h1 (dataset:'z.data_large(keep=(id var1 var2))';
      h1.defineKey('id');
      h1.defineData('var1','var2');
      h1.defineDone();
    end;
  set z.all_sites;
  do while(h1.do_over() eq 0);
    output;
  end;
run;

http://support.sas.com/documentation/cdl/en/lecompobjref/69740/HTML/default/viewer.htm#p07odyzmifa4y3n10pkcvfdbtn4l.htm

Re: DATA MERGE on large datasets but SAS runs out of memory

Cruise — Tue, 23 Apr 2019 14:44:18 GMT

@Patrick I get this error.

254  data O.MERGED;
255    if _n_=1 then
256      do;
257        if 0 then set R.data_large(keep=(id var1 var2));
                                   -
                                   214
                                   23
258        dcl h1 (dataset:'R.data_large(keep=(id var1 var2))';
               --
               565
ERROR: DATA STEP Component Object failure.  Aborted during the COMPILATION phase.
ERROR 214-322: Variable name ( is not valid.

ERROR 23-7: Invalid value for the KEEP option.

ERROR 565-185: H1 is not a known class name.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

259        h1.defineKey('id');
260        h1.defineData('var1','var2');
261        h2.defineDone();
262      end;
263    set R.all_sites;
264    do while(h1.do_over() eq 0);
265      output;
266    end;
267  run;

Re: DATA MERGE on large datasets but SAS runs out of memory

novinosrin — Tue, 23 Apr 2019 14:54:10 GMT

To whom it may concern,

I think the very moment the use of do over aka find_next before 9.4 equivalent is in place, and should the look up be 1:N , dcl hash h1 (dataset:'large',multidata:'y'); would be required.

@Cruise That's just a syntax error

Here is the correction

dcl hash h1 (dataset:'R.data_large(keep=(id var1 var2))';