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;



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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