SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Frequent Contributor
Posts: 75

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

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.

Super User
Super User
Posts: 7,042

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

Posted in reply to NonSleeper

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.;

Frequent Contributor
Posts: 75

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

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.

Super User
Super User
Posts: 7,042

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

Posted in reply to NonSleeper

Probably the formatting in Excel is hiding the seconds.

Ask a Question
Discussion stats
  • 3 replies
  • 291 views
  • 3 likes
  • 2 in conversation