How do i output data with one single observation(01,04 in the following)?
data have;
input ID $ date date9. number;
format date date9.;
datalines;
01 01jan2020 2
03 05jan2020 11
05 14jan2019 9
02 01jan2020 5
02 14jan2021 4
02 22jan2021 12
02 21jan2021 1
03 13feb2020 21
04 02feb2020 12
;
run;
Or
proc sql;
create table WANT as
select *
from HAVE
group by ID
having count(*) = 1;
quit;
I think it's also the observation of ID=05.
proc sort data=have;
by id;
run;
data want;
set have;
by id;
if first.id and last.id;
run;
or
proc sql;
create table want(drop=cnt id2) as
select * from have
left join (select distinct count(*) as cnt, id as id2
from have
group by id)
on have.id=id2
where cnt=1;
quit;
Or
proc sql;
create table WANT as
select *
from HAVE
group by ID
having count(*) = 1;
quit;
if you dont want to use any PROC like SQL and SORT, then hash object can be used here in just one DATA step:
data want;
if _N_=1 then do;
declare hash h();
h.defineKey('ID');
h.defineData('ndup');
h.defineDone();
call missing(ndup);
do _i_=1 to nobs;
set have point=_i_ nobs=nobs;
if h.find()^=0 then do;
ndup=1;
h.add();
end;
else do;
ndup=ndup+1;
h.replace();
end;
end;
end;
set have;
if h.find()=0 and ndup=1 then do;
drop ndup;
output;
end;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.