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;
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
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
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.