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 |
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;
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;
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*/
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.