topic Re: Merge large datasets using sql or harsh objects (need help!!) in SAS Programming

Merge large datasets using sql or harsh objects (need help!!)

constancezeng — Fri, 30 Aug 2019 19:43:09 GMT

Hey guys. New sas user here. I am having trouble merging two super large datasets named ibes and combine (around 30g). I've tried using proc sql but it took me forever and still I cannot have the results.

So I am thinking about merging those two datasets based on sedol=cusip (same company id but different var names in ibes and combine) and ANNDATS<=date<=ANNDATS+365. So each date will correspond to a PRC in combine; and each price in ibes will have 200 PRC (from combine). At the end I need to calculate the number of PRCs for each price in ibes dateset.

I read some posts that suggested a use of hush objects. Here is my attempt:

But somehow sas has returned me an error: : Variable cusip has been defined as both character and numeric..

Please let me know if you have better ideas in terms of how to deal with those two datasets! Appreciate for your help!!

Re: Merge large datasets using sql or harsh objects (need help!!)

novinosrin — Fri, 30 Aug 2019 21:11:46 GMT

1. Why do you need a length or attrib statement when at compilation SAS will read the descriptor portion of the dataset and creates a PDV to hold an observation(record) at execution?

2. Hash object variables inherit their type/length from PDV host variables. In your case I can't see any variable(like that of assignment /explicit retain)outside of the PDV host that is loaded/retrieved in/from hash object. --Very important fundamental !!!!!!!

3. prc=.; date=.; *Why play with representation when call missing will assign missing values appropriately pertaining to type inherited from PDV host variable

 rc=group.find(key:sedol);

 if rc ne 0 then call missing(prc,date);

Re: Merge large datasets using sql or harsh objects (need help!!)

constancezeng — Fri, 30 Aug 2019 20:37:56 GMT

Hey novinosrin! Thank you so much for the reply. I guess you can tell that I don't know much about SAS while I am struggling to learn by working... Do you have any suggestions? I am trying the following code:

Re: Merge large datasets using sql or harsh objects (need help!!)

novinosrin — Fri, 30 Aug 2019 20:43:59 GMT

Fair enough. This time your approach syntactically is fine. Did it meet your logical requirement i.e did you get the results what you need?

You are the best judge to assess the results.

Re: Merge large datasets using sql or harsh objects (need help!!)

Tom — Fri, 30 Aug 2019 20:47:34 GMT

If you don't define the variables before naming them in the HASH object method calls then the data step compiler will define them as numeric.

I usually use a line like this to define the variables from the existing dataset before creating the hash object without actually reading in any of the data.

if 0 then set dsname;

Re: Merge large datasets using sql or harsh objects (need help!!)

novinosrin — Fri, 30 Aug 2019 20:59:05 GMT

@Tom wrote:

If you don't define the variables before naming them in the HASH object method calls then the data step compiler will define them as numeric.

I usually use a line like this to define the variables from the existing dataset before creating the hash object without actually reading in any of the data.
if 0 then set dsname;

Thank you Sir . I am afraid PDV host gets the precedence whether or not variables are defined before naming in Hash obj

For example,

data _null_ ;
   dcl hash H () ;
   h.definekey  ("name") ;
   h.definedata ("name","height","weight") ;
   h.definedone () ;
name="xxx";
height=6868;
weight=5676;
rc=h.add();
h.output(dataset:'test');
stop;
run;

proc contents data=test;
run;

Or a better example

data _null_ ;
   dcl hash H () ;
   h.definekey  ("name") ;
   h.definedata ("name","height","weight") ;
   h.definedone () ;
do until(z);
 set sashelp.class end=z;
 rc=h.add();
end;
h.output(dataset:'test');
stop;
run;

Re: Merge large datasets using sql or harsh objects (need help!!)

Astounding — Fri, 30 Aug 2019 22:29:22 GMT

How many records are in each of your data sets, as you begin? (Ballpark number is OK.)

Re: Merge large datasets using sql or harsh objects (need help!!)

SASKiwi — Fri, 30 Aug 2019 22:36:35 GMT

Have you tried a simple DATA step MERGE? This requires sorted or indexed keys though. If you are essentially joining all rows from both source tables, performance will be limited by IO anyway, regardless of what joining technique you use.

You should also explore other options like using COMPRESS to shrink the size of your SAS datasets to begin with. If your datasets are wide and contain a lot of text then compressing can really speed things up.

Re: Merge large datasets using sql or harsh objects (need help!!)

constancezeng — Fri, 30 Aug 2019 23:19:51 GMT

I have around 300,000,000 rows in combine and 16,000,000 rows in ibes. Thanks for asking!

Re: Merge large datasets using sql or harsh objects (need help!!)

constancezeng — Fri, 30 Aug 2019 23:22:02 GMT

Yes I tried. Both merge and sql gave me an error says im running out of space. And I used the option compress already. Thank you for your suggestions!

Re: Merge large datasets using sql or harsh objects (need help!!)

SASKiwi — Fri, 30 Aug 2019 23:31:27 GMT

In my experience COMPRESS = BINARY is best. Are you running SAS on a PC or a remote server? You need to fix the out of space issue first as any join technique is going to need a similar amount of free space. Your SAS administrator should be able to help if you are using a remote SAS server.

Re: Merge large datasets using sql or harsh objects (need help!!)

Tom — Fri, 30 Aug 2019 23:35:42 GMT

You want to compare 300M records to each of 16M records? No wonder your query never finished.

Are your source datasets already sorted?

What are the min/max number of records per BY group for both of the files? Perhaps one of those will be small enough that you can load each BY group from one of the files into memory/hash.

Re: Merge large datasets using sql or harsh objects (need help!!)

novinosrin — Sat, 31 Aug 2019 00:11:22 GMT

A technique that can be used to reduce the hash memory footprint and still avoid sorting the entire file is to index it first by the splitting variable and then use by-processing, For example:


proc sql noprint;                                                              
  create index cusip on ibes (cusip) ;   
  create index cusip on combine (cusip) ;  
quit 

data want;
if _n_=1 then do;
 dcl hash H (multidata:'y') ;
 h.definekey  ("cusip") ;
 h.definedata ("ANNDATS", "value") ;
 h.definedone () ;
 end;
 do until(last.cusip);
  set ibes(in=a) combine(in=b);
  by cusip;
  if a then rc=h.add();	/*load ibes by group in hash*/
  else do;
   /*Here when b do your find,find_next retrieve and
   whatever logic want*/
  end;
 end;
 h.clear();
 drop rc;
run;

This way, the hash memory footprint is confined to the extent of the largest CUSIP by-group, at the expense of the overhead of creating the index and indexed data retrieval..HTH

Re: Merge large datasets using sql or harsh objects (need help!!)

hashman — Sat, 31 Aug 2019 03:05:15 GMT

@constancezeng:

1. From what you've shown, you have a data type conflict between SEDOL defined as numeric in the LENGTH statement and its character type in the data sets you're processing (it contains alpha data, so it can't be numeric). First the compiler sees the LENGTH statement and stores SEDOL in the compiler symbol table as numeric; then it sees COMBINE referenced in the first SET and reads its descriptor. But in the latter, SEDOL is defined as character, hence the conflict. If you haven't seen a related error before, you will. Solution: Eliminate the LENGTH statement. (In the docs, it's used to create a PDV host variable for the corresponding hash variable, but you don't need it here since the compiler creates it using its attributes stored in the descriptor of COMBINE.)

2. I don't see from your code how the CUSIP data type conflict is possible. It cannot have anything to do with the hash table, as no such hash variable is defined. Judging from your code, CUSIP can only come from IBES since it's not kept in COMBINE and not defined elsewhere in the step. Hence, the code that resulted in this error cannot be the code you showed.

3. As @novinosrin said, it's safer to use CALL MISSING (PRC, DATE), though in your case assigning standard numeric missing values to these variables explicitly doesn't hurt, as both are numeric. However, in your case what you're effectively executing is an inner join: If SEDOL isn't found in IBES, you don't output the record. Therefore, you don't need to set PRC and DATE to missing at all; so, just remove the corresponding statements.

4. Memory shortage is what you may encounter once your step has compiled successfully and commences on loading the table GROUP. If you're on a 64-bit system, its item length is 64 if SEDOL is $16 or shorter and 80 if it's longer. With 16 million items loaded, you're looking at about 1.25 GB of RAM footprint. If your SAS session is allotted 4 GB (which is not much to ask), you should be okay.

5. The reason your shot at doing it with SQL has bombed is most likely an attempt by SQL to sort COMBINE behind the scenes. Since you want an inner join, you can change this default behavior by coding the MAGIC=103 option with the proc SQL statement. It will force the SQL optimizer to select the SQXJHSH access method - in other words, to store IBES in an internal hash table and look it up for every record in COMBINE without sorting the latter; i.e. to do implicitly what you're trying to do by using the hash explicitly. Note that it will only work for an inner join (not left, right, etc.), but since that's what you need, you're in luck here.

6. Yet another option is: Index the file IBES and use the index to look it up for every record in COMBINE using the SET statement with the KEY= option. You can find a gazillion SAS articles on how to do that (including handling duplicate keys on the driver side) or just read the definitive opus on SAS indexes by Michael Raithel:

https://www.amazon.com/Complete-Guide-SAS-Indexes/dp/1590478495

The method is slower than a hash (or implicitly hashed SQL), but it's advantage is that you don't have to worry about the memory footprint.

Kind regards

Paul D.

Re: Merge large datasets using sql or harsh objects (need help!!)

hashman — Sat, 31 Aug 2019 04:20:53 GMT

@Tom:

>If you don't define the variables before naming them in the HASH object method calls then the data step compiler will define them as numeric.<

Nope. The compiler doesn't see anything inside the DefineKey or DefineData methods' parentheses, as they're executed after it has already done its job, i.e. at the run time. As far as the hash object directives go, the compiler isn't concerned with anything but (a) the correctness of the object.dot syntax, (b) making sure that the non-scalar variable (used as the hash object name, such as H) is defined only once, and (c) checking that if a hash object is referenced by a method, operator, or attribute, it's been defined.

In other words, if you do this:

1 data _null_ ;                                                                 
2   dcl hash h () ;                                                             
3   h.definekey ("name") ;                                                      
4   h.definedata ("weight") ;                                                   
5   h.definedone () ;                                                           
6 run ;                                                                         
                                                                                
ERROR: Undeclared key symbol name for hash object at line 5 column 3.           
ERROR: DATA STEP Component Object failure.  Aborted during the EXECUTION phase.

the step compiles just fine and doesn't define any non-auto PDV variables at all; the compiler sees neither NAME nor WEIGHT. It is during the run time (aka EXECUTION phase in the hash log speak) that DefineDone checks for the presence of NAME in the PDV, and having not found it, aborts the step (it doesn't even bother to go on to check for WEIGHT). But if you code:

data _null_ ;                  
  if 0 then set sashelp.class ;
  dcl hash h () ;              
  h.definekey ("name") ;       
  h.definedata ("weight") ;    
  h.definedone () ;            
  stop ;                       
run ;

everything is hunky-dory. However, if you coded:

data _null_ ;              
  dcl hash h () ;          
  h.definekey ("name") ;   
  h.definedata ("weight") ;
  h.definedone () ;        
  stop ;                   
  set sashelp.class ;      
run ;

everything is fine, too. DefineDone (at run time) couldn't care less where in the code - before it or after it - the compiler has seen NAME and WEIGHT at compile time; all it cares about is that the host variables called NAME and WEIGHT are present in the PDV when DefineDone is called at run time. Note that if your assertion that these variables are defined as numeric if the compiler sees them after the hash definition cluster were true, the compiler would abort the step at compile time, as there would be a data type conflict between NAME coming from SASHELP.CLASS and DefineKey upstream.

I more than understand that from the standpoint of an experienced pre-hash SAS programmer, this kind of behavior looks odd. Indeed, before the advent of the hash object, we had accustomed that if a variable is named anywhere in the step, the compiler sees it and compiles it according to its set of (fairly convoluted) rules. When I got my hands on the early version 9.0 circa 2002, I was struck by this behavior, just like anyone in my shoes would be. Luckily, for a length of time I was in direct email contact with the developers, who helped me absorb the concept of parameter type matching rather quickly - in fact, one of them wrote, verbatim, "the compiler doesn't see anything within those parentheses", at which point if kind of instantly clicked.

This is the very reason @DonH and I dedicated the whole section 2.2 (CREATE Operation) to illuminating the things of this nature from different angles in:

https://www.amazon.com/Management-Solutions-Using-Table-Operations/dp/1629601438

Kind regards

Paul D.

Re: Merge large datasets using sql or harsh objects (need help!!)

constancezeng — Sat, 31 Aug 2019 04:21:50 GMT

Hey Paul. I'd like to first thank you and @novinosrin for your answers. I just tried the magic option that you suggested, unfortunately I do not think that I have sufficient space according to SAS.

In addition to that, using what novinosrin suggested, SAS has produced an output. But you can see that a lot of observations are dropped - I am still trying to figuring the reason, maybe I coded it in a wrong way again (I have indexed cusip in both datasets).

Again, thanks to all your comments! I appreciate for your work and looking forward to your reply!

Re: Merge large datasets using sql or harsh objects (need help!!)

hashman — Sat, 31 Aug 2019 04:53:49 GMT

@constancezeng:

You just don't have enough space in your WORK library to write the output data set you are writing.

As long as this is the case, it doesn't matter what kind of file matching method you're using.

Either clean your WORK library of the junk you don't need or work with your admin to give you more disk space in WORK (or write to a different, permanent, library where you do have enough space).

From your log, SQL reacts as I've expected: Note the "merge join has been transformed into a hash join" message.

And yes, you're creating an inner join: (a) because of how your SQL query is structured and (b) otherwise, you wouldn't receive the above message from the optimizer; no join other than an inner join can be prodded into hashing by MAGIC=103.

If you indeed want a left join, you have to code it explicitly. But remember that MAGIC=103 won't work in this case (it makes no sense to me and a number of other big data guys I know, but the fact is, it won't).

Two more suggestions:

Turn on the system options FULLSTIMER to see memory usage.
Turn on the system option MSGLEVEL=I and add the option _METHOD (in addition to MAGIC=103) to the SQL statement to see the SQL access methods reported in the log.

In fact, since you are dealing with fairly large data, I'd recommend keeping these system options on at all times, i.e. just code:

option fullstimer msglevel=i ;

The more relevant info in the SAS log, the better.

Kind regards

Paul D.

Re: Merge large datasets using sql or harsh objects (need help!!)

Kurt_Bremser — Sat, 31 Aug 2019 05:15:37 GMT

Do you get ERRORs or WARNINGs? Is the result not what you expected?

Please post the complete log of the step (copy/paste into a window opened with the {i} button).

For posting code, just copy/paste it into a "little running man" window. Don't post pictures.

Re: Merge large datasets using sql or harsh objects (need help!!)

DonH — Sat, 31 Aug 2019 12:19:21 GMT

A corollary to @hashman's suggestion about WORK, if you have access to a location with more space, define a libname to it (e.g., a libname like MYWORK) and then use the USER option to have SAS use that location for any data sets that have a one level name. That allows you to use a different location for your temporary data sets without having to update each reference.

Just make sure to delete all the SAS datasets you created. If the location only contains your WORK data sets (e.g., by creating a sub-folder in that location with more space), you can simply run something like to delete them all:

proc datasets lib=mywork nolist kill;
run;

Re: Merge large datasets using sql or harsh objects (need help!!)

DonH — Sat, 31 Aug 2019 12:29:14 GMT

As a followup to @hashman's spot-on comments, you might want to review the communities article The SET Statement's Compile Time Functions that provides some additional context for why you sometimes see a conflict about numeric for character conflicts when defining hash objects.

The key point is that the compiler does not recognize quoted strings as variable names (as @hashman said in his comment). If you never reference the variable that is defined to the hash object that is a quoted string in your DATA step, you get the execution time message Undeclared key symbol . . . If you do reference it elsewhere in your program, the compiler will add it to the PDV at compile time and typically will define the variable to the PDV as a numeric variable. The article above provides some examples.