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

Hello,

 

I have the below dataset.

I want another variable which has

  • format of datetime20.
  • with last day of the respective month.(eg: for 201701 the date should be last date of the month --31JAN2017:00:00:00)
data have;
input character_date $6.;
datalines;
201701
201702
201703
201801
201802
201803
201901
201902
201903
;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Looks like this does what you request:

data have;
input character_date $6.;
datetime = dhms(intnx('month',input(character_date,yymmn6.),0,'e'),0,0,0);

format datetime datetime18.;
datalines;
201701
201702
201703
201801
201802
201803
201901
201902
201903
;
run;

Input with the correct informat will get you the first day of the month, the INTNX function with the 'e' alignment gets the last day of the month and the DHMS function turns a date and hour=0,minute=0 and second=0 into the desired datetime.

View solution in original post

1 REPLY 1
ballardw
Super User

Looks like this does what you request:

data have;
input character_date $6.;
datetime = dhms(intnx('month',input(character_date,yymmn6.),0,'e'),0,0,0);

format datetime datetime18.;
datalines;
201701
201702
201703
201801
201802
201803
201901
201902
201903
;
run;

Input with the correct informat will get you the first day of the month, the INTNX function with the 'e' alignment gets the last day of the month and the DHMS function turns a date and hour=0,minute=0 and second=0 into the desired datetime.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 4355 views
  • 1 like
  • 2 in conversation