topic Re: Merge with Hash Object in SAS Programming

Merge with Hash Object

ssafmed — Wed, 09 Aug 2023 13:51:56 GMT

Hello,

I want to merge 2 tables using Hash Object by keyA and keyB that have the same values(but not the same name in the tables), but in my output I want just a few values from both tables,

Example

table A : keyA, Cl_A_1, Cl_A_2, Cl_A_3, Cl_A_4, CL_A5

table B : keyB, Cl_B_1,Cl_B_2,Cl_B_3,Cl_B_4,Cl_B_5,Cl_B_6

------------------------------------------------------------------

Output : KeyA, keyB, Cl_A_1, Cl_A_2, Cl_B_1,Cl_B_2,Cl_B_3

Thanks!

Re: Merge with Hash Object

mkeintz — Wed, 09 Aug 2023 22:19:40 GMT

Plesse provide sample data in the form of a working DATA step, and a working DATA step with the desired result.

With that clarification, we'll be able to determine a way to get from sample input to desired output, with tested code.

Help us help you.

Re: Merge with Hash Object

Patrick — Wed, 09 Aug 2023 23:38:20 GMT

Easiest is to just rename the key variable to what's in your base table when loading into the hash table.

dcl hash h1 (dataset:'your_table(rename=(keyB=KeyA))');

"but in my output I want just a few values from both tables"

You need to further specify what this is about AND you need to provide sample data and desired result. Please provide the sample data via a working SAS datastep that creates such data.

Re: Merge with Hash Object

ssafmed — Thu, 10 Aug 2023 12:15:22 GMT

Here is an example :

data class;

input Name$ Sex$ Age Height Weight;

infile datalines dlm=' ' dsd;

datalines;

Alfred M 14 69.0 112.5

Alice F 13 56.5 84.0

Barbara F 13 65.3 98.0

Carol F 14 62.8 102.5

Henry M 14 63.5 102.5

;

data cities;

input zipcode City :$30. Department :$30. Persona:$10.;

infile datalines dlm=',' dsd;

datalines;

75008, Paris 8è ,Paris,Alice

91940, Les Ulis, Essonne,Carol

92330,Sceaux, Hauts-de-Seine,Henry

93140, Bondy , Seine-Saint-Denis,Henry

94150, Rungis, Val-de-Marne,Alice

;

data Set1 ;

if 0 then set class cities;

if _n_ = 1 then do;

declare Hash Match1 (dataset:'class');

Match1.DefineKey ('Name');

Match1.DefineData ('Name','Sex','Age');

Match1.DefineDone ();

end;

set cities;

if Match1.find(key:Persona) = 0 then output;

run;

What I want is an output set that contains Name Sex Age Department! I can do it with (keep=Name Sex Age Department), but I dont want to process this variables!

Thanks

Re: Merge with Hash Object

Kurt_Bremser — Thu, 10 Aug 2023 16:07:50 GMT

Add these two statements to your IF N_ = 1 block:

length name $8 sex $8 age 8;
call missing(name,sex,age);

You have to tell the data step compiler about the new variables, so it can compile them into the PDV. The second statement is just there to avoid the "uninitialized" NOTE.

Re: Merge with Hash Object

ssafmed — Thu, 10 Aug 2023 17:15:50 GMT

Hello,

In fact, in my output I just want de keep Name Sex Age and department and not (zipcode, City, Persona)

I dont even want that the variables (zipcode, City, Persona) went to PVD

Thanks

Re: Merge with Hash Object

Kurt_Bremser — Thu, 10 Aug 2023 17:30:21 GMT

Use either the DROP= or KEEP= dataset option in your SET statement.

Re: Merge with Hash Object

ssafmed — Thu, 10 Aug 2023 18:04:55 GMT

In this case it will be process in PDV, but I want to avoid that for efficiency because my data are very big!

Thanks

Re: Merge with Hash Object

Kurt_Bremser — Thu, 10 Aug 2023 18:34:09 GMT

When you exclude variables in the SET statement, which controls the reading of a dataset, those variables will not be part of the PDV. You can verify this with a PUT _ALL_; statement.

BTW since the whole dataset file will be p?hysically read anyway, the number of variables in the PDV won't have an influence on processing time, only on memory used, and with so few as you describe, the effect of dropping them will be nil.

Re: Merge with Hash Object

ssafmed — Thu, 10 Aug 2023 19:03:25 GMT

I put a keep just after If 0 (I dont know if it is that you mean by Set reading statment)

but still processing all the variables in PVD (please see log below)

Code :

if 0 then set class cities;

keep Name Sex Age Persona Department;

if _n_ = 1 then do;

declare Hash Match1 (dataset:'class');

Match1.DefineKey ('Name');

Match1.DefineData ('Name','Sex','Age');

Match1.DefineDone ();

end;

set cities ;

if Match1.find(key:Persona) = 0 then output;

PUT _ALL_;

run;

log :

NOTE: There were 5 observations read from the data set WORK.CLASS.

Name=Alice Sex=F Age=13 Height=. Weight=. zipcode=75008 City=Paris 8è Department=Paris Persona=Alice _ERROR_=0 _N_=1

Name=Carol Sex=F Age=14 Height=. Weight=. zipcode=91940 City=Les Ulis Department=Essonne Persona=Carol _ERROR_=0 _N_=2

Name=Henry Sex=M Age=14 Height=. Weight=. zipcode=92330 City=Sceaux Department=Hauts-de-Seine Persona=Henry _ERROR_=0 _N_=3

Name=Henry Sex=M Age=14 Height=. Weight=. zipcode=93140 City=Bondy Department=Seine-Saint-Denis Persona=Henry _ERROR_=0 _N_=4

Name=Alice Sex=F Age=13 Height=. Weight=. zipcode=94150 City=Rungis Department=Val-de-Marne Persona=Alice _ERROR_=0 _N_=5

data Set1;

Thanks

Re: Merge with Hash Object

Kurt_Bremser — Thu, 10 Aug 2023 19:51:41 GMT

This is a KEEP Statement, not a KEEP= Dataset Option.

Re: Merge with Hash Object

Patrick — Thu, 10 Aug 2023 22:49:52 GMT

Below how I would code for this. Make sure that you load the table with the lower volume into the hash. That's from a memory consumption and performance perspective much more important than if you've got a few more variables than you would need in the PDV. As long as you only write variables to the output table that you need (drop/keep) the difference to performance is negligible.

data Set1;
  if _n_ = 1 then
    do;
      if 0 then set class(keep=name sex age);
      declare Hash Match1 (dataset:'class');
      Match1.DefineKey ('Name');
      Match1.DefineData ('Name','Sex','Age');
      Match1.DefineDone ();
    end;
  call missing(of _all_);

  set cities(keep=Persona department);

  if Match1.find(key:Persona) = 0;

  /* potentially more statements */
  drop persona;
run;

Also be aware that at least in your sample data column Persona has a length of 10 but Name only a length of 8. Should the string length stored in Persona exceed 8 characters then the hash lookup will return an error like: ERROR: Argument length greater than length of key variable Name

Re: Merge with Hash Object

ssafmed — Fri, 11 Aug 2023 11:07:29 GMT

Hello,

It's what I did but still not solve the problem!

Thanks!

if 0 then set class cities;

keep Name Sex Age Persona Department;

if _n_ = 1 then do;

declare Hash Match1 (dataset:'class');

Re: Merge with Hash Object

Kurt_Bremser — Fri, 11 Aug 2023 15:38:33 GMT

@ssafmed wrote:

Hello,

It's what I did but still not solve the problem!

Thanks!

if 0 then set class cities;

keep Name Sex Age Persona Department;

if _n_ = 1 then do;

declare Hash Match1 (dataset:'class');

You are still using the statement instead of the dataset option. Please read the documentation I linked for you.

Re: Merge with Hash Object

Tom — Fri, 11 Aug 2023 16:07:16 GMT

Let's see what your code does:

if 0 then set class cities;

Since SAS will evaluate zero as FALSE the SET statement will never run. But the data step compiler will see it and add all of the variables from both the CLASS and CITIES datasets.

keep Name Sex Age Persona Department;

This says to only output these 5 specific variables. If CLASS is like SASHELP.CLASS then the first three will be defined as in CLASS and the second two will be defined as in CITIES. If any of those five variables do not exist in either CLASS or CITIES and are not later defined explicitly or implicitly by other code in the step then SAS not create them and instead write a WARNING message to the SAS log.

Perhaps you meant to do something like:

if 0 then set class(keep=Name Sex Age) cities(keep=Persona Department);

Now the data step compile step will only add those specific 5 variables. But you will have to also check other places where you use either CLASS or CITIES in another SET or MERGE statement to make sure that does not bring in other variables.

Re: Merge with Hash Object

dxiao2017 — Fri, 04 Jul 2025 18:17:33 GMT

Hi this is the answer base on others' reply and codes:

data class;
input Name$ Sex$ Age Height Weight;
datalines;
Alfred M 14 69.0 112.5
Alice F 13 56.5 84.0
Barbara F 13 65.3 98.0
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5
;
run;
data cities;
input zipcode City :$30. Department :$30. Persona:$10.;
infile datalines dlm=',' dsd;
datalines;
75008, Paris 8è ,Paris,Alice
91940, Les Ulis, Essonne,Carol
92330,Sceaux, Hauts-de-Seine,Henry
93140, Bondy , Seine-Saint-Denis,Henry
94150, Rungis, Val-de-Marne,Alice
;
run;
proc print data=class;run;
proc print data=cities;run;
data set1;
   if _n_=1 then do;
      if 0 then set class(keep=name sex age);
      declare hash match1(dataset:'class');
      match1.definekey('name');
      match1.definedata('sex','age');
      match1.definedone();
   end;
   set cities(keep=persona department);
      name=persona;
      rc=match1.find(key:name);
      if rc=0;
      drop persona rc;
run;
proc print data=set1;run;

Re: Merge with Hash Object

Tom — Fri, 04 Jul 2025 22:11:23 GMT

That seems to work.

The extra assignment statement

name=persona;

will prevent the issue mentioned above about values of PERSONA that are longer than the 8 bytes reserved for the NAME variable used as the key in the hash object. The assignment operation will truncate the value to fit into the NAME variable.

Note you could have done that in the .FIND() method call and avoided the need for the assignment statement. If you know the length needed you could put PUT function with $ format.

match1.find(key: put(persona,$8.) )

And if you don't then use VLENGTH() to find it and use the SUBSTRN() function to truncate.

match1.find(key:substrn(persona,1,vlength(name)))