BookmarkSubscribeRSS Feed
Pgloria
Obsidian | Level 7

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

3 REPLIES 3
ballardw
Super User

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.

Pgloria
Obsidian | Level 7

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;

Tom
Super User Tom
Super User

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

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
  • 1973 views
  • 0 likes
  • 3 in conversation