DATA Step, Macro, Functions and more

move specific records on the top by date.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

move specific records on the top by date.

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;


Accepted Solutions
Solution
‎04-09-2014 11:53 AM
PROC Star
Posts: 7,468

Re: move specific records on the top by date.

Posted in reply to davidnamh

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;

View solution in original post


All Replies
Super User
Posts: 19,772

Re: move specific records on the top by date.

Posted in reply to davidnamh

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;

Solution
‎04-09-2014 11:53 AM
PROC Star
Posts: 7,468

Re: move specific records on the top by date.

Posted in reply to davidnamh

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;

Respected Advisor
Posts: 3,156

Re: move specific records on the top by date.

Posted in reply to davidnamh

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

Occasional Contributor
Posts: 17

Re: move specific records on the top by date.

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

Occasional Contributor
Posts: 17

Re: move specific records on the top by date.

Posted in reply to davidnamh

Thank you everyone for the codes !

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 213 views
  • 6 likes
  • 4 in conversation