topic Re: How to output single observation in a dataset? in SAS Programming

How to output single observation in a dataset?

jude1 — Wed, 24 Feb 2021 02:10:49 GMT

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;

Re: How to output single observation in a dataset?

japelin — Wed, 24 Feb 2021 02:32:12 GMT

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;

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;

Re: How to output single observation in a dataset?

ChrisNZ — Wed, 24 Feb 2021 02:54:47 GMT

proc sql;
  create table WANT as
  select * 
  from HAVE
  group by ID
  having count(*) = 1;
quit;

Re: How to output single observation in a dataset?

LeonCathay — Wed, 24 Feb 2021 04:11:14 GMT

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;