CODEWITHSUNDEEP
sas
Neal801
Neal801

Reputation: 307

SAS-Data selection within certain requirements

I have a dataset as shown in this screenshot. In each day, each interval (e.g. 9:30:00) has multiple, repeated _RIC. For example, observation 2 and 3 (DDA211204700) are repeated.

enter image description here

I'd like to select every first _RIC in each interval in each day. For example, for 20120103, 09:30:00, I want to pick up Observation 1, 2, 4, 6, and so on.

I used the following code:

data test1;
 do until (last.interval);
set test;
   by _ric date_L_ interval;
   if first._ric;
   output;
 end;
run;

Although the code seems to work as shown in this next screenshot, I still hope someone could help me to check my code because I really have little experience with SAS. Thanks!

enter image description here

Upvotes: 1

Views: 31

Answers (2)

Shenglin Chen
Shenglin Chen

Reputation: 4554

It seems that you want to get the earliest time_L_ in group, you could also try this:

proc sql;
   select * from have group by _ric,interval having time_L_=min(time_L_);
quit;

Upvotes: 1

Tom
Tom

Reputation: 51621

Your data is not ordered properly to detect the first record for each _RIC within an INTERVAL. First sort the data properly and then your logic might work. Also there is a logic error in using the subsetting IF statement inside of a DOW loop since it will abort the outer DO loop. You wanted to just use a normal IF/THEN statement instead (if first._ric then output;) . But you really don't need a DOW loop for this situation. So we could use a subsetting IF.

You could sort by INTERVAL and then _RIC and date.

data WANT ;
  set HAVE ;
  by interval _ric date_L_ ;
  if first._ric;
run;

Or you could get the same records if you sorted by _RIC and then INTERVAL and date and use FIRST.INTERVAL instead.

Upvotes: 2

Related Questions