Solved
New Contributor
Posts: 2

# keep the median value for every timestamp

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

Accepted Solutions
Solution
‎07-27-2014 06:42 AM
Super User
Posts: 10,205

## Re: keep the median value for every timestamp

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

All Replies
Solution
‎07-27-2014 06:42 AM
Super User
Posts: 10,205

## Re: keep the median value for every timestamp

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

New Contributor
Posts: 2

## Re: keep the median value for every timestamp

Hey Ksharp,

I works! many thanks you really save my day.

Kind Regards,

Zhihong

Super User
Posts: 10,205

## Re: keep the median value for every timestamp

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

🔒 This topic is solved and locked.