topic Re: converting 7 digit date to regular date in SAS Programming

converting 7 digit date to regular date

devsas — Thu, 21 Sep 2017 16:06:34 GMT

Good Afternoon everyone! So, I have got these dates which look like these:-

1980901, 1990831, 2011011, 2130801

but which mean actually 19980901, 19990831, 20011011, 20130801

Basically you need to replace first digit by 19 if the first digit is 1 and by 20 if the first digit is 2. I tried the following code, and not sure what im doing incorrect.

data test;

input date_ ;

cards;

1980901

1990831

2011011

2130801

;

run;

data want;

set test ;

LENGTH DATE1 $12 ;

if substr(date_, 1, 1) = 1 then date1 = 19||substr(date_, 2, 6) ;

ELSE IF substr(date_, 1, 1) = 2 then date1 = 20||substr(date_, 2, 6) ;

RUN;

Re: converting 7 digit date to regular date

Reeza — Thu, 21 Sep 2017 16:14:06 GMT

Date_ as a numeric variable so use SUBSTRN instead of SUBSTR.

The variables you're creating are character and SUBSTR returns a character value so you need quotes. Same with CAT/||, it's character so it needs quotes.

Last but not least, please use the code editor to enter your code and format it for legibility in the future.

Here's a worked solution and an example of creating an actual SAS date out of the values.

data test;
	input date_;
	cards;
1980901
1990831
2011011
2130801
;
run;

data want;
	set test;
	LENGTH DATE1 $8;

	if substrn(date_, 1, 1)='1' then
		date1='19'||substrn(date_, 2);
	ELSE IF substrn(date_, 1, 1)='2' then
		date1='20'||substrn(date_, 2);
	sas_date=input(put(date1, 8.), yymmdd8.);
	format sas_date date9.;
RUN;

Re: converting 7 digit date to regular date

ballardw — Thu, 21 Sep 2017 16:22:23 GMT

I have to say that I've seen a fair number of mangled dates but this is new way to me.

Are all of your dates 7 characters or is this only for some of them in the data?

Re: converting 7 digit date to regular date

devsas — Thu, 21 Sep 2017 17:21:06 GMT

No, they are all 7 digits from what I know. They are formatted as number though.

Re: converting 7 digit date to regular date

Tom — Thu, 21 Sep 2017 17:24:37 GMT

The B8601CI. informat can read date strings in the format CYYMMDDHHMMSS, but the meaning of the Century value is a little different. 0 means 1900 and 1 means 2000. So just subtract 1,000,000 from your number before converting it to a string and addting the zeros for the time part. Use the DATEPART() function to convert back to a date variable instead of a datetime variable.

data have;
input date_ ;
cards;
1980901
1990831
2011011
2130801
;
run;

data want ;
  set have ;
  date = datepart(input(put(date_-1000000,z7.)||'000000',B8601CI.));
  format date yymmdd10. ;
run;

Re: converting 7 digit date to regular date

devsas — Thu, 21 Sep 2017 17:55:28 GMT

Thanks Reeza. One thing I noticed in your code was having additonal put inside the input function-

sas_date=input(put(date1, 8.), yymmdd8.);

But if I remove that and use just

sas_date=input(date1, yymmdd8.);

it works fine too.

So, not sure whats the purpose of put function here?

Re: converting 7 digit date to regular date

Reeza — Thu, 21 Sep 2017 19:04:29 GMT

It's likely possible I made a mistake.

If date1 is numeric but in the format you want, ie 20170101, then you would use PUT. If it's already character you don't need the PUT.