topic date formats in SAS Programming

date formats

PBsas — Fri, 14 Jul 2017 15:03:05 GMT

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.

Re: date formats

milts — Fri, 14 Jul 2017 15:10:21 GMT

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#n0bj9p4401w3n9n1gmv6tf**bleep**9m.htm

Re: date formats

ballardw — Fri, 14 Jul 2017 15:13:24 GMT

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

Re: date formats

PBsas — Fri, 14 Jul 2017 15:18:12 GMT

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.

Re: date formats

PBsas — Fri, 14 Jul 2017 15:24:32 GMT

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

Re: date formats

Astounding — Fri, 14 Jul 2017 16:13:32 GMT

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

Re: date formats

PBsas — Tue, 18 Jul 2017 21:13:43 GMT

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