Hello All,
I have a sample dataset as below.
data have;
format ref_date_1 ref_date_2 mmddyy10. clm_date1 clm_date2 datetime16.;
informat ref_date_1 ref_date_2 mmddyy10. clm_date1 clm_date2 datetime16.;
input claim_id ref_date_1 ref_date_2 clm_date1 clm_date2;
datalines;
1123524 5/12/2017 4/18/2012 05MAY17:17:09:00 26MAY17:13:45:00
1123524 5/13/2017 4/19/2012 16MAY17:17:11:50 23MAY17:17:23:00
1123524 5/14/2017 4/20/2012 05MAY17:17:09:01 04MAY17:19:40:00
1123524 5/15/2017 4/21/2012 16MAY17:17:11:51 26MAY17:13:45:01
1123524 5/16/2017 4/22/2012 05MAY17:17:09:02 23MAY17:17:23:01
1123524 5/17/2017 4/23/2012 16MAY17:17:11:52 04MAY17:19:40:01
1123524 5/18/2017 4/24/2012 05MAY17:17:09:03 26MAY17:13:45:02
1123524 5/19/2017 4/25/2012 16MAY17:17:11:53 23MAY17:17:23:02
;
run;
I need all these dates converted to mmddyy10. format and store them in new variables. The problem is that I have many variables like these and I don't know which variable has which format. What I can see is mmddyy10. and datetime16. are most prevalent.
I have tried this below but this is not feasible because first there are many variable and second I don't know which has what format. Is there any way I can read all variables with some informat?
data want(drop=ref_date_1 ref_date_2 clm_date1 clm_date2);
format ref_date_1_new ref_date_2_new clm_date1_new clm_date2_new mmddyy10.;
set have;
ref_date_1_new = ref_date_1;
ref_date_2_new = ref_date_2;
clm_date1_new = datepart(clm_date1);
clm_date2_new = datepart(clm_date2);
run;
I am using SAS 9.4.
Your help is appreciated.
If you know something about the years involved (such as they are all within the past 30 years or so), this approach would work.
Step 1: Read in all variables as dates.
Step 2: Convert the datetimes by having SAS examine the values:
if ref_date1 > '01jan2050'd then ref_date1 = datepart(ref_date1);
You might want to explore reading them as strings first then use prxmatch to check the pattern so you could convert them with the proper informat.
Usage of prxmatch can be found here
I have never used prxmatch before. Looks like the link is broken. I will take a look if prxmatch can be used. Thanks.
@PBsas wrote:
Hello All,
I need all these dates converted to mmddyy10. format and store them in new variables. The problem is that I have many variables like these and I don't know which variable has which format. What I can see is mmddyy10. and datetime16. are most prevalent.
Your help is appreciated.
WHY?
You can specify the Format to use at any given time.
If you want to convert datetime to dates that's one thing but see:
data _null_; set have; put ref_date_1= mmddyy10. ref_date_1= date9. ref_date_1= yyq.; run;
If I want the date to be used one way in one procedure and differently in an other procedure then just specify the format for each procedure:
proc print data=have;
var ref_date_1;
format ref_date_ ddmmyy10.;
run;
proc print data=have;
var ref_date_1;
format ref_date_ worddate.;
run;
I need to convert datetime to date. I have many variables and I don't know which has datetime and which has date in original data set.
If you know something about the years involved (such as they are all within the past 30 years or so), this approach would work.
Step 1: Read in all variables as dates.
Step 2: Convert the datetimes by having SAS examine the values:
if ref_date1 > '01jan2050'd then ref_date1 = datepart(ref_date1);
All my dates are within past 4-5 years so this will work. Thanks @Astounding
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.