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

[constancezeng]

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!!
 

[novinosrin]

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);

[constancezeng]

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: 

[novinosrin]

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.

[Kurt_Bremser]

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.

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

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;

[novinosrin]

---

@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;

[hashman]

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

[DonH]

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.

[Astounding]

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

[constancezeng]

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

[Tom]

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.

[SASKiwi]

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.

[constancezeng]

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!

[SASKiwi]

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.