DATA Step, Macro, Functions and more

converting 7 digit date to regular date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 118
Accepted Solution

converting 7 digit date to regular date

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;


Accepted Solutions
Solution
‎09-21-2017 01:20 PM
Super User
Posts: 23,296

Re: converting 7 digit date to regular date

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;

 

 

 

View solution in original post


All Replies
Solution
‎09-21-2017 01:20 PM
Super User
Posts: 23,296

Re: converting 7 digit date to regular date

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;

 

 

 

Frequent Contributor
Posts: 118

Re: converting 7 digit date to regular date

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?

Super User
Posts: 23,296

Re: converting 7 digit date to regular date

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.

Super User
Posts: 13,321

Re: converting 7 digit date to regular date

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?

Frequent Contributor
Posts: 118

Re: converting 7 digit date to regular date

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

Super User
Super User
Posts: 7,934

Re: converting 7 digit date to regular date

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;

image.png

 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 337 views
  • 7 likes
  • 4 in conversation