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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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;

 

 

 

devsas
Pyrite | Level 9

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?

Reeza
Super User

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.

ballardw
Super User

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?

devsas
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4369 views
  • 7 likes
  • 4 in conversation