topic Re: How to create a numeric measurement reflecting time in quite a mixed format? in SAS Data Management

How to create a numeric measurement reflecting time in quite a mixed format?

NonSleeper — Thu, 05 Mar 2015 04:00:27 GMT

So my data comes from an online survey and consists of, among others, two variables: ID and Time of submission. The later is a string variable and looks like this:

2 February 2015 7:18 PM

The question arises from the fact that some participants submitted their responses more than once. So I want to select the most recent submission and drop others. Can you suggest a macro-free program that can do that work? I've got stuck in figuring out a numeric measurement reflecting time of submission from the original variable or something like that which will help with sorting.

Any thoughts are appreciated.

Re: How to create a numeric measurement reflecting time in quite a mixed format?

Tom — Thu, 05 Mar 2015 04:41:26 GMT

Note sure how a macro could help with this type of problem.

Just convert the text variable to a numeric variable. Then you can sort and take the last one.

dt = input(time_of_submission,anydtdtm.);

format dt datetime19.;

Re: How to create a numeric measurement reflecting time in quite a mixed format?

NonSleeper — Fri, 06 Mar 2015 05:36:09 GMT

It's interesting that when I import the data from excel, SAS automatically converts the submission variable to date format. For example, the value of "26 February 2015 7:19 PM" becomes "26FEB15:19:19:49". I check the format and it's DATETIME16.; I guess the length is the reason the seconds shows up though they aren't in the original variable. I change the format to DATETIME14. and things look pretty good now.

Re: How to create a numeric measurement reflecting time in quite a mixed format?

Tom — Sat, 07 Mar 2015 01:29:12 GMT

Probably the formatting in Excel is hiding the seconds.