BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Arpit_Agarwal
Fluorite | Level 6

Hello experts,

 

I am struggling to convert the sas datetime values with AM/PM at the end to something like yyyymmddhhmmssffffff.

 

Let's say -

 

Input value :

2/8/2017 8:10:41 AM

2/24/2017 11:38:15

 

My expected value :

20170208081041000000

20170224113815000000  (ffffff can be put as 000000 for the last 6 digits at the end for all records).

 

How do I do that? Please advise.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Couple of questions, how do you know 11:24 is pm, is it the abscence of AM?  Also, the output format is not like any date time I have ever seen, are you really sure you want to do that?

Also in the future post test data in the form of a datastep, I am going to assume that field is character:

 

data want;
  length thedt want $200;
  infile datalines dlm="¬";
  input thedt $;
  dpart=input(scan(thedt,1," "),mmddyy10.);
  tpart=input(scan(thedt,2," "),time8.);
  if scan(thedt,3," ") ne "AM" then tpart=tpart+"12:00"t;
  want=cats(compress(tranwrd(put(dpart,yymmdd10.),"-","")),compress(tranwrd(put(tpart,tod8.),":","")));
  format dpart date9. tpart time8.;
datalines;
2/8/2017 8:10:41 AM
2/24/2017 11:38:15
;
run;

Note the above is abit verbose to show workings.  

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

Such values can be read with the anydtdtm. format. Take special care of the missing AM/PM, as in your second example; within an AM/PM context, that value is ambiguous.

AFAIK, there is no SAS datetime format that creates your output in one step.

You could try to roll your own picture format (described here), or you could extract date and time using the datepart() and timepart() functions, and use formats without separators (eg yymmddn8.) to build your string.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Couple of questions, how do you know 11:24 is pm, is it the abscence of AM?  Also, the output format is not like any date time I have ever seen, are you really sure you want to do that?

Also in the future post test data in the form of a datastep, I am going to assume that field is character:

 

data want;
  length thedt want $200;
  infile datalines dlm="¬";
  input thedt $;
  dpart=input(scan(thedt,1," "),mmddyy10.);
  tpart=input(scan(thedt,2," "),time8.);
  if scan(thedt,3," ") ne "AM" then tpart=tpart+"12:00"t;
  want=cats(compress(tranwrd(put(dpart,yymmdd10.),"-","")),compress(tranwrd(put(tpart,tod8.),":","")));
  format dpart date9. tpart time8.;
datalines;
2/8/2017 8:10:41 AM
2/24/2017 11:38:15
;
run;

Note the above is abit verbose to show workings.  

Arpit_Agarwal
Fluorite | Level 6
Worked fine in PC SAS, however, when I am trying to run my sas code in unix environment, I am getting following errros -

The format $YYMMDD was not found or could not be loaded.

The format $TOD was not found or could not be loaded.


Is there a reason for why it would error out?
ballardw
Super User

Please post the code with error messages from the log. Use the {i} menu icon in the forum to open a code box that preserves the format of the log.

 

The message $yymmdd not found usually results from attempting to apply a date format, which uses numeric values, to a character value. We would have to see the entire code you used to know exactly what is triggering this.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

It is one of two things, either your program is not the same between the two systems, or your data is not the same between the two systems.  As I can't see either data or programs at either stage I don't see how I could say?

Arpit_Agarwal
Fluorite | Level 6

Thank you RW9.

 

This is how it worked for me -

a) Let's assume - original datasset is A

b) I had to take out the date field and the KEY in a new dataset, say B.

c) I applied above logic on B to create C with the new date field having values in the format I was interested in.

d) Merged A and C, on the basis of the KEY.

 

..and I do not know how to explain why it would not work on the original dataset only.

 

Appreciate your help!

Arpit_Agarwal
Fluorite | Level 6

Thank you everyone, RW9's solution worked for me. But a huge thanks for each one who responded! Much thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 6796 views
  • 1 like
  • 4 in conversation