BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kjohnsonm
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

6 REPLIES 6
Haikuo
Onyx | Level 15

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
ballardw
Super User

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.

kjohnsonm
Lapis Lazuli | Level 10

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.

kjohnsonm
Lapis Lazuli | Level 10
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!
ballardw
Super User

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.

kjohnsonm
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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