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,364

Re: move specific records on the top by date.

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: 17,912

Re: move specific records on the top by date.

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,364

Re: move specific records on the top by date.

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,124

Re: move specific records on the top by date.

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.

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.

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

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