Hello All,
I have an old dataset I am reading from a DBF file it has 6 fields that are dates and they each are showing data type at "$6." no quotes in proc contents. I need to get them into mmddyy8. Can someone help me figure this out?
The strings are clean save there are some that are null and some that have 000000 or 999999. Otherwise they are 100 percent clean with the format yymmdd (no delimiter)
I can parse the field into yy mm and dd then recombine it in any order I need.
but just have no idea where to go from here... (or if parsing is needed)
data temp;
length BEGIN_DATEn $8.;
/*length BEGIN_DATEz 8.;*/
set original;
BEGIN_DATEn=input(substr(strip(BEGIN_DATE),3,2),$2.)||"/"||input(substr(strip(BEGIN_DATE),5,2),$2.)||"/"||input(substr(strip(BEGIN_DATE),1,2),$2.);
BEGIN_DATEz=input(BEGIN_DATEn,mmddyy.8);
format BEGIN_DATEz mmddyy8.;
run;
proc print data=temp (obs=500);
var BEGIN_DATE BEGIN_DATEn
/*BEGIN_DATEz*/
;
run;
... and yep have not tried to deal with the blanks or 999999 or 000000 data yet, I can't get dates to work for one field that is clean without these issues... d'oh! TIA -kj
999999 or 000000 or blank is easy to tackle, I will leave it for you. Before the code, I want to inform you of system option YEARCUTOFF, depending on your data, it may make a big difference, google it and make it right for you if the default (YEARCUTOFF=1920) does not suit you.
For the clean ones:
23 data _null_; 24 old='050131'; 25 new=input(old,yymmdd6.); 26 format new mmddyy8.; 27 put new=; 28 run; new=01/31/05
999999 or 000000 or blank is easy to tackle, I will leave it for you. Before the code, I want to inform you of system option YEARCUTOFF, depending on your data, it may make a big difference, google it and make it right for you if the default (YEARCUTOFF=1920) does not suit you.
For the clean ones:
23 data _null_; 24 old='050131'; 25 new=input(old,yymmdd6.); 26 format new mmddyy8.; 27 put new=; 28 run; new=01/31/05
What you need to know is if the 000000 or 999999 mean specific types of missing values such as "not recorded" or "invalid collected date" so you can treat them appropriately.
What @Haikuo didn't explicitly say is that since the special values are not valid dates they will result in missing values.
So the concept of special missing values may apply if you might need to treat those values differently in the future.
I sure was making it harder than it needed to be...
It seems to me I need to learn the order of operation for data steps. Thanks for your help with this.
Here is an example of possible use of special missing with your data.
proc format library=work;
invalue SpecialDate
000000=.S
999999=.E
other=[MMDDYY6.]
;
Value SpecialDate
.S = 'Missing Start Date'
.E = 'Missing End Date'
other= [MMDDYY10.]
;
run;
data example ;
informat mydate SpecialDate.;
Format Mydate SpecialDate.;
input mydate;
datalines;
000000
999999
050613
041210
121212
;
run;
proc print data=example noobs;
format mydate SpecialDate;
run;
One warning with use of special missing: Some procedures will treat these differently than simple missing such as counts in Proc freq.
But the technique is well worth knowing when you get coded values mixed in with others.
That is very cool.
proc print data=example noobs;
run;
The SAS System 14:14 Monday, May 2, 2016 21
mydate
Missing Start Date
Missing End Date
05/06/2013
04/12/2010
12/12/2012
I would not have thought to look for any commands like this. Thank you very much. -KJ
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.