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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.