How do I get the correct date format and filter out accordingly?

[StickyRoll]

I have a source data whereby I need to identify the correct date format in a dataset.

data want;
  birthdate=19881231; output;
  birthdate=19670628; output;
  birthdate=09301999l output;
run;

A macro will pass the birthdate format for futher filtering. For example, if the macro parameter is YYYYMMDD, only the first 2 birthdate can be matched. Likewise, if the macro parameter is MMDDYYYY, only the 3rd row record can be matched.

Previously, I have a script in the following:

birthdatewant=input(birthdate,anydtdte.);

However, the script will recognize all 3 as valid date. For my case, I want to recognize based on the format passed in (either in YYYYMMDD or MMDDYYYY dynamically).

Is there a way for me to achieve this?

[japelin]

First,
Is birthdate type numeric or character?
If it is numeric, 09301999 is handled as 9301999.
If it is character, quote is needed.

With that out of the way, for the actual code, try the following.

data have;
  birthdate='19881231'; output;
  birthdate='19670628'; output;
  birthdate='09301999'; output;
run;

data want;
  set have;
  birthdatewant=input(birthdate,??yymmdd8.);
  if birthdatewant=. then birthdatewant=input(birthdate,??mmddyy8.);
  format birthdatewant yymmdd10.;/* date format as you want */
run;

[s_lassen · Accepted solution]

You want to know what the format of the datestring is?

You can try something like this

data have;                                             
  birthdate='19881231'; output;                        
  birthdate='19670628'; output;                        
  birthdate='09301999'; output;                        
run;                                                   
                                                       
data test;                                             
  set have;                                            
  birthdatewant=input(birthdate,anydtdte.);            
  if birthdate=put(birthdatewant,yymmddn8.) then       
    InputFormat='CCYYMMDD';                            
  else if birthdate=put(birthdatewant,ddmmyyn8.) then  
    InputFormat='DDMMCCYY';                            
  else if birthdate=put(birthdatewant,mmddyyn8.) then  
    InputFormat='MMDDCCYY';                            
run;

I changed the input fields to character, as that makes the code easier to work with.

The dates in you example are unambiguous, but there may be stuff like '02032012' which can be both DDMMCCYY and MMDDCCYY. When you use the ANYDTDTE informat, remember to set the DATESTYLE option to what you want.