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?
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.
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;
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.
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.