I have a table that contains strings containing dates in a variety of formats (e.g. some have values like '20110701', others like '01Jul11') and need to be able to dynamically reformat back to the SAS numeric equivalents. These strings are not in a consistent format so I can't use an input statement with a hard-coded informat to do this. I am able to derive the informats that I'd need to use for each row but still can't reformat without errors.
For example, if I create a single row equivalent using the following data step:
data tmp;
Char_date='20110701';
Date_format='yymmdd8.';
run;
What I actually want is to be able to derive a third variable, Num_date, from the values in Char_date and date_format, i.e. doing the equivalent of an input statement.
input(Char_date,date_format);
doesn't work because Date_format is parsed as a character string rather than the keywords for a SAS format.
As an alternative, I've tried to create macro variables for the format in each row using the following.
data _null_;
set tmp;
call symputx('DateFormat'||strip(_N_),Date_format);
run;
However, when I attempt to dynamically reference these macro variables, then I get the same problem as above:
data tmp2;
set tmp;
Num_Date=input(Char_date,symget('DateFormat'||strip(_N_)));
run;
With the value returned by the call to symget being a string, rather than the format value. I think that this approach can work because if I hard code the macro variable reference as follows:
data tmp2;
set tmp;
Num_date=input(Char_Date,&DateFormat1);
run;
Then I get the results that I'd expect. So, it seems that I'm missing something in how to dynamically reference the macro variables created in the null data step above. I've looked at this so often now that I'm probably missing something really obvious so apologies if this is the case.
Can anyone tell me what I'm doing wrong?
Thanks for your help.
Use the INPUTN function which allows the second argument to be a variable name.
data tmp;
Char_date='20110701';
Date_format='yymmdd8.';
num_date = inputn(char_date,date_format);
put (char_date date_format num_date) (=) num_date = date9. ;
run;
Char_date=20110701 Date_format=yymmdd8. num_date=18809 num_date=01JUL2011
While I think that Tom has already answered your question, I have to ask:
did you try using the anydtdte informat? I have found it to be quite powerfull.
Tom/Art
Thanks guys. I knew that I was missing something obvious. We did look at other informats including anydtdte but there are some pretty obscure values that this doesn't work for.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: