Merge two datasets and remove duplicates

[bhca60]

I want to merge flu and members data sets then remove duplicates:

proc sql;
create table flu
as select *
from x.personImmunization
where (cpt in
("90630" "90653" "90654" "90655" "90656" "90657" "90658" "90661" "90662" "90666" "90667" "90668" "90673" "90674" "90682" "90685" "90686" "90687" "90688" "90756"
"G0008" "Q2034" "Q2035" "Q2036" "Q2037" "Q2038" "Q2039"))
and sourceSystem="xxxx" and immunizationDate between "2020-08-01" and "2021-03-31";
quit;

/*get a list of the patients that are in the vaccination list so that we only select their proc codes*/
/*from personAttribution table*/
proc sql;
create table members
as select *
from x.personAttribution
where memberNo in (select patient_id from shp_flu)
and eligYear in (2020,2021);
quit;

/*merge flu and members tables together and bring back those that are in flu table*/

data vac_list;
merge flu (IN=A)
          members (IN=B);
by personid;
if A and B; 
run;

ERROR: BY variables are not properly sorted on data set WORK.MEMBERS.

[unison · Accepted solution]

Hi there,
You need to order both datasets by the “by” variable(s) (personid in your case) before the merge step

[Kurt_Bremser]

Please post the complete log from all three steps by copy/pasting it into a window opened with this button: