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;
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;
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;
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;
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
Thank you so much for your help ! I really appreciate it so much.
Thank you everyone for the codes !
Every codes in the thread works good. They all returned the output as I wanted to see.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.