BookmarkSubscribeRSS Feed
jpm2478
Calcite | Level 5

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

 

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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;

novinosrin
Tourmaline | Level 20

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;
jpm2478
Calcite | Level 5
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;

novinosrin
Tourmaline | Level 20

 

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*/

 

ballardw
Super User

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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2021 views
  • 1 like
  • 3 in conversation