DATA Step, Macro, Functions and more

sorting character values which contains date and time

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 85
Accepted Solution

sorting character values which contains date and time

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


Accepted Solutions
Solution
‎04-27-2015 01:50 PM
Super User
Posts: 17,960

Re: sorting character values which contains date and time

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;

View solution in original post


All Replies
Super User
Posts: 10,550

Re: sorting character values which contains date and time

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;

Super Contributor
Posts: 426

Re: sorting character values which contains date and time

Substr function should return only '2015' for the first record. Can you please tell me how it produces '20150402'?

Super User
Super User
Posts: 7,430

Re: sorting character values which contains date and time

substr(logs,5,8)


Starting at position 5, take the next 8 characters = 20150402.

Solution
‎04-27-2015 01:50 PM
Super User
Posts: 17,960

Re: sorting character values which contains date and time

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;

Respected Advisor
Posts: 3,906

Re: sorting character values which contains date and time

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;

Trusted Advisor
Posts: 1,300

Re: sorting character values which contains date and time

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) «$»

Super User
Posts: 9,691

Re: sorting character values which contains date and time

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 296 views
  • 3 likes
  • 8 in conversation