topic Re: Compare two datasets using Hash and update values in SAS Procedures

Compare two datasets using Hash and update values

jpm2478 — Tue, 17 Jul 2018 21:37:35 GMT

Hi,

I have 2 datasets which I need to compare multiple ways and update values in one column. I have datasets A & B.

Question 1

I need to compare set B with A using Hash and if there are identical values then for those rows I need to delete the Ind. value in 3rd column. I only need to delete the date filed in 3rd column but still keep the row.

Question 2

I need to compare set B with A using Hash and if there are NEW values in Set B then I need to add them to Set A and add todays date to 3rd column.

Work.A
Id	Name	Ind.
1	Lin	6/15/2018
2	Fin	7/15/2018
3	Min	7/15/2018
4	Tin	6/15/2018

Work.B
Id	Name
1	Lin
8	Fin
5	Min
4	Tin
10	Quin

Re: Compare two datasets using Hash and update values

novinosrin — Tue, 17 Jul 2018 21:55:20 GMT

See if this matches your expected output, or let me know

data a;
input Id	Name $	Ind mmddyy10.;
format ind mmddyy10.;
cards; 
1	Lin	6/15/2018
2	Fin	7/15/2018
3	Min	7/15/2018
4	Tin	6/15/2018
;
 
data b;
input	Id	Name $	;
cards; 
1	Lin
8	Fin
5	Min
4	Tin
10	Quin
;

data _null_;
if _n_=1 then do;
if 0 then set a;
 dcl hash H (dataset:'a',ordered: "A") ;
   h.definekey  ("id","name") ;
   h.definedata ("id","name","Ind") ;
   h.definedone () ;
 end;
 set b end=last;
 if h.find()=0 then do; call missing(ind);h.replace();end;
 else do;ind=today();h.replace();end;
 if last then h.output(dataset:'want');
 run;

Re: Compare two datasets using Hash and update values

novinosrin — Tue, 17 Jul 2018 22:12:29 GMT

Just for fun, swapping hash obj

data want;
if _n_=1 then do;
if 0 then set a;
 dcl hash H (dataset:'b',ordered: "A") ;
   h.definekey  ("id","name") ;
   h.definedata ("id","name") ;
   h.definedone () ;
   dcl hiter i('h');
 end;
 set a end=last;
 if h.find()=0 then do; call missing(ind);h.remove();end;
 output;
 if last then do;
 rc = i.first();
 do while (rc = 0);
  ind=today();
  output;
  rc = i.next();
 end;
 end;
 drop rc;
 run;

Re: Compare two datasets using Hash and update values

jpm2478 — Tue, 17 Jul 2018 22:17:37 GMT

Can you please explain these two lines....
if h.find()=0 then do; call missing(ind);h.replace();end;
else do;ind=today();h.replace();end;

Re: Compare two datasets using Hash and update values

novinosrin — Tue, 17 Jul 2018 22:22:07 GMT

if h.find()=0 then do; call missing(ind);h.replace();end; /*This does your 1st requirement ---if there are identical values then for those rows I need to delete the Ind. value in 3rd column,find the matches, call missing assigns nulls to the date value and loads back in the hash using replace*/

else do;ind=today();h.replace();end;/* this one does your 2nd req--if there are NEW values in Set B then I need to add them to Set A and add todays date to 3rd column and loads back using replace*/

Re: Compare two datasets using Hash and update values

ballardw — Tue, 17 Jul 2018 22:50:05 GMT

And a non-hash solution that I think works since no example output was provided...

data a;
input Id	Name $	Ind :mmddyy10.;
format ind mmddyy10.;
cards; 
1	Lin	6/15/2018
2	Fin	7/15/2018
3	Min	7/15/2018
4	Tin	6/15/2018
;
run; 
data b;
input	Id	Name $	;
ind=.;
cards; 
1	Lin
8	Fin
5	Min
4	Tin
10	Quin
;
run;
proc sort data=a;
  by id Name;
run;
proc sort data=b;
   by id Name;
run;

data a;
  update a (in=ina) b (in=inb) updatemode=nomissingcheck;
  by id Name;
  if inb and not ina then ind=today();

run;