DATA Step, Macro, Functions and more

Macro isdate

Reply
Occasional Contributor
Posts: 14

Macro isdate

Hello,

As a way to improve sql joins with other rdbms I wanted to build a macro that give a datetime value or a column containing datetime values it would output only the year and month.

If I ever pass a hardcoded value of a date then it would return the value using a sysfunc (in order to make our joins faster), if it was a column value or any other expression it would use the put function.

I want something like this:

%macro converto_yyyymm(value);

     %if &value is hardcoded date (either in '31dec2011:00:00:00'dt or 1672531200 formats)

     %then %sysfunc(%sysevalf(&value),yymmn6.);

     %else put(&value,yymmn6.);

%mend;

But I still cound not find a way to make that %if condition, anyone has ideias on this?

Best regards

Super User
Posts: 10,500

Macro isdate

How will the result of your code be used? In a data step? value in line somewhere else?

If you provide an example of how the macro is to be called in code and the expected output you'll get better help.

Occasional Contributor
Posts: 14

Macro isdate

It is to be used with data integration studio, so it will be in a where clause in a proc sql

ex:

proc sql;

select * from oracle_table

where month=%converto_yyyymm(&data_macro_var);

quit;

or

proc sql;

select * from table1, table2

where table1.month_column=%converto_yyyymm(table2.date_column)

quit;

Super User
Super User
Posts: 6,500

Macro isdate

I do not understand why you think you need a macro for this.

If you have a value in a macro variable then just use it in the PUT statement the same as you would a variable reference.

Here are some examples.

put(&value,yymmn6.)

Valid values for the macro variable VALUE could be a date literal, an actual date value as the number of days since 1/1/1960, or the name of a variable that has a date.

"01JAN2012"d

18993

table2.date_column

Ask a Question
Discussion stats
  • 3 replies
  • 889 views
  • 0 likes
  • 3 in conversation