BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jude1
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

 

Or

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

 

 

 

View solution in original post

3 REPLIES 3
japelin
Rhodochrosite | Level 12

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;
ChrisNZ
Tourmaline | Level 20

 

Or

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

 

 

 

LeonCathay
SAS Employee

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;



hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1276 views
  • 3 likes
  • 4 in conversation