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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
milts
Pyrite | Level 9

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

PBsas
Obsidian | Level 7

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

ballardw
Super User

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

PBsas
Obsidian | Level 7

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.

 

Astounding
PROC Star

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

 

 

PBsas
Obsidian | Level 7

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

 

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
  • 6 replies
  • 886 views
  • 0 likes
  • 4 in conversation