DATA Step, Macro, Functions and more

Referencing a macro variable as a format

Reply
Occasional Contributor
Posts: 7

Referencing a macro variable as a format

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.

Super User
Super User
Posts: 7,079

Referencing a macro variable as a format

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

PROC Star
Posts: 7,492

Referencing a macro variable as a format

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.

Occasional Contributor
Posts: 7

Referencing a macro variable as a format

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.

Ask a Question
Discussion stats
  • 3 replies
  • 208 views
  • 7 likes
  • 3 in conversation