topic Re: move specific records on the top by date. in SAS Programming

move specific records on the top by date.

davidnamh — Wed, 09 Apr 2014 15:39:13 GMT

Hi, I would like to move the records to the top row if t >= 21:00 ( previous day) and t <0:00 (before next day). Thank you so much !

data have;

infile datalines delimiter=',';

input d $ t $;

datalines;

1/1/2014,00:00

1/1/2014,01:00

1/1/2014,02:00

1/1/2014,21:00

1/1/2014,22:00

1/1/2014,23:00

1/2/2014,0:00

1/2/2014,1:00

1/2/2014,2:00

1/2/2014,3:00

;

run;

data want;

infile datalines delimiter=',';

input d $ t $;

datalines;

1/1/2014,21:00

1/1/2014,22:00

1/1/2014,23:00

1/1/2014,00:00

1/1/2014,01:00

1/1/2014,02:00

1/2/2014,0:00

1/2/2014,1:00

1/2/2014,2:00

1/2/2014,3:00

;

run;

Re: move specific records on the top by date.

Reeza — Wed, 09 Apr 2014 15:45:54 GMT

create a new variable, and sort by that one.

data have;

set have;

if t>75600 then sort_date=intnx('day', d, -1);

run;

proc sort data=have out=want;

by date sort_date time;

run;

Re: move specific records on the top by date.

art297 — Wed, 09 Apr 2014 15:53:36 GMT

Basically the same suggestion as Fareeza, but to do either you have to create date and time fields. e.g.:

data have;

infile datalines delimiter=',';

input d $ t $;

datalines;

1/1/2014,00:00

1/1/2014,01:00

1/1/2014,02:00

1/1/2014,21:00

1/1/2014,22:00

1/1/2014,23:00

1/2/2014,0:00

1/2/2014,1:00

1/2/2014,2:00

1/2/2014,3:00

;

run;

data want;

set have;

date=input(d,mmddyy10.);

time=input(t,time5.);

timegroup=ifn(time ge 75600,1,2);

run;

proc sort data=want;

by date timegroup time;

run;

Re: move specific records on the top by date.

Haikuo — Wed, 09 Apr 2014 16:41:14 GMT

In line with Reeza and Art, a SQL solution:

data have;

infile datalines delimiter=',';

input d :mmddyy10. t time5.;

format d mmddyy10. t time5.;

datalines;

1/1/2014,00:00

1/1/2014,01:00

1/1/2014,02:00

1/1/2014,21:00

1/1/2014,22:00

1/1/2014,23:00

1/2/2014,0:00

1/2/2014,1:00

1/2/2014,2:00

1/2/2014,3:00

;

run;

proc sql;

create table want (drop=_group) as

select *, case when t>='21:00't then 0 else 1 end as _group

from have

order by d, calculated _group, t

;

quit;

Haikuo

Re: move specific records on the top by date.

davidnamh — Wed, 09 Apr 2014 16:54:35 GMT

Thank you so much for your help ! I really appreciate it so much.

Re: move specific records on the top by date.

davidnamh — Wed, 09 Apr 2014 16:59:04 GMT

Thank you everyone for the codes !

Every codes in the thread works good. They all returned the output as I wanted to see.