07-22-2011 11:01 AM
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:
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.
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.
However, when I attempt to dynamically reference these macro variables, then I get the same problem as above:
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:
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.
07-22-2011 11:25 AM
Use the INPUTN function which allows the second argument to be a variable name.
num_date = inputn(char_date,date_format);
put (char_date date_format num_date) (=) num_date = date9. ;
Char_date=20110701 Date_format=yymmdd8. num_date=18809 num_date=01JUL2011
07-22-2011 11:30 AM
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.
07-22-2011 11:43 AM
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.