hash - Hash Merge Macro - 使用文件记录指示器“HASH + point = Key”

Question

希望将此宏更新为 HASH + point = key。对于我们的一次数据运行，我们已经开始使用当前版本的此宏超出内存限制。我寻求帮助的原因是因为我没有太多时间，也从未真正分析过这段代码，因为直到最近它才成为我流程的一部分。

我不太了解https://www.lexjansen.com/nesug/nesug11/ld/ld01.pdf是如何设置 RID 以及如何将其合并到我们的宏中。实际上，我什至不知道是否可以使用我们当前的宏来做到这一点。

任何帮助将不胜感激。

%macro hashmerge2(varnm,onto,from,byvars,obsqty);

%let data_vars   = %trim   (&varnm);
%let data_vars_a = %sysfunc(tranwrd(&data_vars.,%str( ),%str(" , ")));
%let data_vars_b = %sysfunc(tranwrd(&data_vars.,%str( ), %str(,)));
%let data_key    = %trim   (&byvars);
%let data_key    = %sysfunc(tranwrd(&data_key.,%str( ), %str(" , ")));

%if %index(&varnm,' ') > 0 %then %let varnm3=%substr(%substr(&varnm,1,%index(&varnm,' ')),1,4);
%else %let varnm3=%substr(&varnm,1,4);


data &onto(drop=rc) miss&varnm3(drop=rc);
if 0 then set &onto &from(keep=&varnm. &byvars.);

 declare hash h_merge (dataset: "&from.");

 rc = h_merge.DefineKey  ("&data_key.");
 rc = h_merge.DefineData ("&data_vars_a.");
 rc = h_merge.DefineDone ();


 do until (eof);
   set &onto end = eof;
   call missing(&data_vars_b.);
   rc = h_merge.find ();
   if rc = 0 then do;
      output &onto;
      from = "&from.";
   end;
   else do;
      output miss&varnm3 &onto;
      from = "&onto.";
   end;
 end;

stop;
run;

%mend;

标签： hashmergesassas-macro

Answer 1

所以我认为这就是你要找的，但它仍然需要将“查找”表中的所有键值加载到哈希对象中。但它可以节省空间，而不是加载非关键变量，它只需要加载与关键变量匹配的观察数。

%macro hash_merge_point
/*-----------------------------------------------------------------------------
Merge variables ONTO large table FROM small table using POINT= dataset option.
-----------------------------------------------------------------------------*/
(varnm  /* Space delimited list of variable to retrieve */
,onto   /* Dataset to update */
,from   /* Dataset to get values from */
,byvars /* Space delimited list of key variables to match on */
);
%local missds key_vars;
%let missds=%scan(&varnm,1,%str( ));
%let missds=miss%substr(&missds,1,%sysfunc(min(28,%length(&missds))));

%let key_vars="%sysfunc(tranwrd(%sysfunc(compbl(&byvars)),%str( )," "))";

data &onto(drop=rc) &missds(drop=rc);
  if 0 then set &onto &from(keep=&varnm. &byvars.);

  declare hash h_merge ();
  rc = h_merge.DefineKey  (&key_vars);
  rc = h_merge.DefineData ('_point');
  rc = h_merge.DefineDone ();
  do _point=1 to _nobs;
    set &from(keep=&byvars) point=_point nobs=_nobs;
    rc = h_merge.add();
  end;

  do until (eof);
    set &onto end = eof;
    rc = h_merge.find ();
    if rc = 0 then do;
      set &from (keep=&varnm) point=_point;
      from = "&from.";
      output &onto;
    end;
    else do;
      call missing(of &varnm);
      from = "&onto.";
      output ;
    end;
  end;

stop;
run;

%mend hash_merge_point;

所以这是一个简单的例子：

data lookup;
  input id age sex $1.;
cards;
1 10 F
2 20 .
4 30 M
;
data master ;
  input id wt ;
cards;
1 100
2 150
3 180
4 200
;

%hash_merge_point
/*-----------------------------------------------------------------------------
Merge variables ONTO large table FROM small table using POINT= dataset option.
-----------------------------------------------------------------------------*/
(varnm=age sex  /* Space delimited list of variable to retrieve */
,onto=master   /* Dataset to update */
,from=lookup  /* Dataset to get values from */
,byvars=id /* Space delimited list of key variables to match on */
);

如果目标表已经包含由合并创建的变量（因此您只想覆盖当前值），那么您可以使用 MODIFY 语句而不是 SET 语句来修改数据集。但您可能希望在尝试此操作之前确保您有表的备份。另请注意，如果您想要源变量的标志from，那么该变量也需要存在。

所以有了这个更新的主表：

data master ;
  input id wt ;
  length age 8 sex $1 from $50;
cards;
1 100
2 150
3 180
4 200
;

而这个版本的宏：

%macro hash_merge_point
/*-----------------------------------------------------------------------------
Merge variables ONTO large table FROM small table using POINT= dataset option.
-----------------------------------------------------------------------------*/
(varnm  /* Space delimited list of variable to retrieve */
,onto   /* Dataset to update */
,from   /* Dataset to get values from */
,byvars /* Space delimited list of key variables to match on */
);
%local key_vars;
%let key_vars="%sysfunc(tranwrd(%sysfunc(compbl(&byvars)),%str( )," "))";

data &onto;
  if 0 then set &onto (keep=&byvars.);

  declare hash h_merge ();
  rc = h_merge.DefineKey  (&key_vars);
  rc = h_merge.DefineData ('_point');
  rc = h_merge.DefineDone ();
  do _point=1 to _nobs;
    set &from(keep=&byvars) point=_point nobs=_nobs;
    rc = h_merge.add();
  end;

  do until (eof);
    modify &onto end = eof;
    rc = h_merge.find ();
    if rc = 0 then do;
      set &from (keep=&varnm) point=_point;
      from = "&from.";
    end;
    else from = "&onto.";
    replace;
  end;

stop;
run;

%mend hash_merge_point;

如果您运行此代码：

proc print data=master; 
 title 'BEFORE';
run;

%hash_merge_point
/*-----------------------------------------------------------------------------
Merge variables ONTO large table FROM small table using POINT= dataset option.
-----------------------------------------------------------------------------*/
(varnm=age sex  /* Space delimited list of variable to retrieve */
,onto=master   /* Dataset to update */
,from=lookup  /* Dataset to get values from */
,byvars=id /* Space delimited list of key variables to match on */
);

proc print data=master; 
  title 'AFTER';
run;

你得到这个结果：