Hi all,
I have few logs read as dataset and has data something like below
Data new;
Input logs $100.;
Datalines;
ABC_20150402_12.31.log
DEF_20150401_23.12.log
New_20150312_06.00.log;
Run;
Here, I want to sort based on date And time. And hence want output as
New_20150312_06.00.log
DEF_20150401_23.12.log
ABC_20150402_12.31.log
Somehow I am unable to get it right. Need suggestio
Many thanks
Create a new variable that has the date and time and sort on that. Otherwise it will sort alphabetically, in this case descending would give you what you want but it's coincidence.
proc sort data=new;
by descending logs;
run;
Read it as date valued or add a variable that is data valued and sort on that.
Data work.new;
length logs $100;
Input logs ;
Logdate =input(substr(logs,5,8),yymmdd8.);
format logdate mmddyy10.;
Datalines;
ABC_20150402_12.31.log
DEF_20150401_23.12.log
New_20150312_06.00.log
;
Run;
Sort on LogDate;
Substr function should return only '2015' for the first record. Can you please tell me how it produces '20150402'?
substr(logs,5,8)
Starting at position 5, take the next 8 characters = 20150402.
Create a new variable that has the date and time and sort on that. Otherwise it will sort alphabetically, in this case descending would give you what you want but it's coincidence.
proc sort data=new;
by descending logs;
run;
With a RegEx:
data have;
Input logs :$100.;
Datalines;
ABCde_xx_20150402_12.31.log
DEF_25_20150401_23.12.log
New_20150312_06.00.log
;
run;
proc sql;
select
*,
input(prxchange('s/^.*(\d{8})_(\d\d)\.(\d\d)\.log$/\1T\2\3/oi',1,strip(logs)),? b8601DT.) as log_date format=datetime21.
from have
order by calculated log_date
;
quit;
Without a RegEx:
Data have;
Input logs :$100.;
format log_date datetime21.;
tmp=substrn(logs,lengthn(logs)-17,14);
tmp=compress(tmp,'.');
tmp=translate(tmp,'T','_');
log_date=input(tmp,b8601DT.);
Datalines;
ABCde_xx_20150402_12.31.log
DEF_25_20150401_23.12.log
New_20150312_06.00.log
;
Run;
proc sql;
select logs, log_date
from have
order by log_date
;
quit;
Here is an alternative using pretty much the same regular expression as used,
data _null_;
attrib logname informat=$22.
logdt informat=b8601dt19.;
declare hash l(ordered:'y');
l.defineKey('logdt');
l.defineData('logname', 'logdt');
l.defineDone();
infile cards eof=done;
do while(1);
input logname @;
_infile_ = prxchange('s#^.*(\d{8})_(\d{2})\.(\d{2})\.log.*$#\1T\2\30000000#o', 1, _infile_);
input @1 logdt ?;
l.add();
end;
done:
l.output(dataset:'foo');
stop;
cards;
ABC_20150402_12.31.log
DEF_20150401_23.12.log
New_20150312_06.00.log
;
run;
^.*(\d{8})_(\d{2})\.(\d{2})\.log.*$
Options: ^ and $ match at line breaks
Assert position at the beginning of a line (at beginning of the string or after a line break character) «^»
Match any single character that is not a line break character «.*»
Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
Match the regular expression below and capture its match into backreference number 1 «(\d{8})»
Match a single digit 0..9 «\d{8}»
Exactly 8 times «{8}»
Match the character “_” literally «_»
Match the regular expression below and capture its match into backreference number 2 «(\d{2})»
Match a single digit 0..9 «\d{2}»
Exactly 2 times «{2}»
Match the character “.” literally «\.»
Match the regular expression below and capture its match into backreference number 3 «(\d{2})»
Match a single digit 0..9 «\d{2}»
Exactly 2 times «{2}»
Match the character “.” literally «\.»
Match the characters “log” literally «log»
Match any single character that is not a line break character «.*»
Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
Assert position at the end of a line (at the end of the string or before a line break character) «$»
Without a RegEx:
Data new;
Input logs $50.;
Datalines;
ABC_20150402_12.31.log
DEF_20150401_23.12.log
New_20150312_06.00.log
;
Run;
proc sql;
create table want as
select *
from new
order by input(compress(logs,,'kd'),best32.);
quit;
Message was edited by: xia keshan
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.