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;
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;
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;
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?
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.
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?
No, they are all 7 digits from what I know. They are formatted as number though.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.