Eliminating all duplicates

Batman — Fri, 21 Aug 2020 17:25:24 GMT

I'm using an SQL join to eliminate all records that have the same three variables. It appears to do I what wanted (it drops 6 records that have the same hicn, yr and month) but I also get a warning. Is there away to change this so I don't get the warning?

27 proc sql;
28 create table Onerecord as
29 select * from ByYrMo a inner join ByYrMo b
30 on a.hicn=b.hicn and a.yr=b.yr and a.mon=b.mon
31 group by a.hicn, a.yr, a.mon
32 having count(*)=1
33 ;
NOTE: The query requires remerging summary statistics back with the original data.
WARNING: Variable HICN already exists on file WORK.ONERECORD.
WARNING: Variable Yr already exists on file WORK.ONERECORD.
WARNING: Variable Mon already exists on file WORK.ONERECORD.
WARNING: Variable FFS_Dis already exists on file WORK.ONERECORD.
NOTE: Table WORK.ONERECORD created, with 8349353 rows and 4 columns.

34 quit;

Re: Eliminating all duplicates

novinosrin — Fri, 21 Aug 2020 17:33:17 GMT

Use a table alias to avoid ambiguity-

proc sql;
create table Onerecord as
select a.* 
from ByYrMo a inner join ByYrMo b
 on a.hicn=b.hicn and a.yr=b.yr and a.mon=b.mon
 group by a.hicn, a.yr, a.mon
 having count(*)=1

topic Eliminating all duplicates in SAS Programming

Eliminating all duplicates

Re: Eliminating all duplicates