BookmarkSubscribeRSS Feed
LosEndos
Calcite | Level 5

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.

3 REPLIES 3
Tom
Super User Tom
Super User

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

art297
Opal | Level 21

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.

LosEndos
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1086 views
  • 7 likes
  • 3 in conversation