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

Hi folks,

I am working on a dataset (have) with each id has multiple rows. I wanna delete the ids for which at least one value of variable diffdate is less than 60. I am struggling with the proper condition. The following are the sample "have" and "want" datasets. Thanks in advanced. 1.JPG2.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Here's an easy way, with a forgiveable flaw (explained below):

 

data want;

merge have (in=delete_me where=(. < diffdate < 60)) have;

by id;

if delete_me then delete;

run;

 

Naturally, the data must be in sorted order by ID.  In addition, there can easily be a note on the log about more than one data set containing multiple observations for the BY variable.  Usually that note should trigger an investigation to see if things are working properly.  In this case, the note is expected and can be ignored.

 

 

 

View solution in original post

4 REPLIES 4
Astounding
PROC Star

Here's an easy way, with a forgiveable flaw (explained below):

 

data want;

merge have (in=delete_me where=(. < diffdate < 60)) have;

by id;

if delete_me then delete;

run;

 

Naturally, the data must be in sorted order by ID.  In addition, there can easily be a note on the log about more than one data set containing multiple observations for the BY variable.  Usually that note should trigger an investigation to see if things are working properly.  In this case, the note is expected and can be ignored.

 

 

 

strqimr
Fluorite | Level 6

Many Thanks

Satish_Parida
Lapis Lazuli | Level 10
proc sql;
	create table want as
		select * from have where id not in(
			select distinct id from have where diffdate<60 or diffdate=.);
quit;
novinosrin
Tourmaline | Level 20
data have;
input id $ diffdate;
datalines;
T10008 .
T10008 25
T10008 125
T10064 .
T10064 100
T10079 .
T10079 253
T10079 36
T10096 .
T10096 58
T10096 32
T10096 39
T10135  .
T10135 147
T10139 .
T10139 98
T10139 80
;

data _null_;
set have;
if _n_=1 then do;
 declare hash h(dataset: 'have', multidata: 'y',ordered:'y');
  h.definekey('id');
   h.definedata('id','diffdate');
  h.definedone();
  end;
set have(where=(. < diffdate < 60)) end=last;
if h.check()=0 then h.remove();
if last then h.output(dataset:'want');
run;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

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
  • 4 replies
  • 22932 views
  • 3 likes
  • 4 in conversation