topic Re: keep the median value for every timestamp in Statistical Procedures

keep the median value for every timestamp

zhfan — Sun, 27 Jul 2014 09:19:07 GMT

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 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!

Re: keep the median value for every timestamp

Ksharp — Sun, 27 Jul 2014 10:42:18 GMT

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

Re: keep the median value for every timestamp

zhfan — Sun, 27 Jul 2014 10:48:43 GMT

Hey Ksharp,

I works! many thanks you really save my day.

Kind Regards,

Zhihong

Re: keep the median value for every timestamp

Ksharp — Tue, 29 Jul 2014 13:23:06 GMT

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