topic Re: Date stored as $6. need to convet to mmddyy8. ?? in SAS Programming

Date stored as $6. need to convert to mmddyy8. ??

kjohnsonm — Fri, 29 Apr 2016 15:22:08 GMT

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

Re: Date stored as $6. need to convet to mmddyy8. ??

Haikuo — Fri, 29 Apr 2016 01:52:23 GMT

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

Re: Date stored as $6. need to convet to mmddyy8. ??

ballardw — Fri, 29 Apr 2016 14:56:07 GMT

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.

Re: Date stored as $6. need to convet to mmddyy8. ??

kjohnsonm — Fri, 29 Apr 2016 15:14:45 GMT

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.

Re: Date stored as $6. need to convet to mmddyy8. ??

kjohnsonm — Fri, 29 Apr 2016 15:20:56 GMT

Yep, no one works here anymore from this time line when the data was created. We think that 999999 000000 mean special things. For example 999999 no end date or TBA and 000000 might be unknown start date or something like that… but until we are sure we are going to keep the original field in the data set as a new name and then join the corrected field with all the other year data sets. Thanks for the advice. Have a great day!

Re: Date stored as $6. need to convet to mmddyy8. ??

ballardw — Mon, 02 May 2016 20:18:45 GMT

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.

Re: Date stored as $6. need to convet to mmddyy8. ??

kjohnsonm — Mon, 02 May 2016 21:34:49 GMT

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