DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

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

[ Edited ]

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


Accepted Solutions
Solution
‎04-29-2016 11:11 AM
Respected Advisor
Posts: 3,124

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

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

View solution in original post


All Replies
Solution
‎04-29-2016 11:11 AM
Respected Advisor
Posts: 3,124

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

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
Super User
Posts: 10,500

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

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.

Frequent Contributor
Posts: 90

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

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.

Frequent Contributor
Posts: 90

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

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!
Super User
Posts: 10,500

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

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.

Frequent Contributor
Posts: 90

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 354 views
  • 4 likes
  • 3 in conversation