- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
No, they are all 7 digits from what I know. They are formatted as number though.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;