BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
davidnamh
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
Reeza
Super User

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;

art297
Opal | Level 21

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;

Haikuo
Onyx | Level 15

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

davidnamh
Calcite | Level 5

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

davidnamh
Calcite | Level 5

Thank you everyone for the codes !

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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