DATA Step, Macro, Functions and more

date formats

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

date formats

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.


Accepted Solutions
Solution
a month ago
Super User
Posts: 5,080

Re: date formats

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

 

 

View solution in original post


All Replies
Super Contributor
Posts: 318

Re: date formats

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

 

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#n0bj9p4401w...

Occasional Contributor
Posts: 19

Re: date formats

I have never used prxmatch before. Looks like the link is broken. I will take a look if prxmatch can be used. Thanks.

Super User
Posts: 10,483

Re: date formats


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;

Occasional Contributor
Posts: 19

Re: date formats

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.

 

Solution
a month ago
Super User
Posts: 5,080

Re: date formats

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

 

 

Occasional Contributor
Posts: 19

Re: date formats

All my dates are within past 4-5 years so this will work. Thanks @Astounding

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 115 views
  • 0 likes
  • 4 in conversation