keep the median value for every timestamp

[zhfan]

Hi everyone,

I'm new to SAS and I would like some help.

I have multiple observations at every timestamp and I want to keep the median value for every timestamp. For example:

what i have is:

date             time            value

20010101   16:33:35      1

20010101   16:33:35      3

20010101   16:33:35      3

20010101   16:33:35      6

20010101   16:33:38      1

20010101   16:33:38      2

20010101   16:33:38      6

what i want is:

date            time            value

20010101   16:33:35      3

20010101   16:33:38      2

What i have found so far is to keep the first or the last observation by using:

*# keep last recorded value;

data hello;

set hello2 (keep=date time value);

by date time;

   if last.time;

format time time.;

run;

my question is is it possible to find the median and put it at the begin or the end for every timestamp , and then apply it with my code? Or do you have a better solution

Many thanks in advance!

[Ksharp · Accepted solution]

If you have SAS9.4 ,you can do it as simple as by SQL.

proc sql;
 select date,time,median(value) as median
  from have 
   group by date,time ;
quit;

If not :

data have;
input date :yymmdd10.            time    : time9.        value ;
format date date. time time.;
cards;
20010101   16:33:35      1
20010101   16:33:35      3
20010101   16:33:35      3
20010101   16:33:35      6
20010101   16:33:38      1
20010101   16:33:38      2
20010101   16:33:38      6
;
run;
data want(drop=n value);
 set have;
 by  date time;
 array x{99999} _temporary_;
 if first.time then do; n=0;call missing(of x{*});end;
 n+1; x{n}=value;
 if last.time then do;median=median(of x{*});output;end;
run;

Xia Keshan

[zhfan]

Hey Ksharp,

I works! many thanks you really save my day.

Kind Regards,

Zhihong

[Ksharp]

Oh, I almost forgot the third way. Sorry.

data have;
input date :yymmdd10.            time    : time9.        value ;
format date date. time time.;
cards;
20010101   16:33:35      1
20010101   16:33:35      3
20010101   16:33:35      3
20010101   16:33:35      6
20010101   16:33:38      1
20010101   16:33:38      2
20010101   16:33:38      6
;
run;
proc summary data=have;
by date time;
var value;
output out=want median=median;
run;

Xia Keshan