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;
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.