Create a macro to hold 1jan2017 using sql

[adityaa9z]

Todays date is 5/23/2017.

I need to create a macro to hold 01/Jan/2017. Using SQL.

[PaigeMiller]

So let's clarify something.

You asked for a macro that holds a text string. Do you mean you want a macro VARIABLE that holds a text string?

Why does it have to be SQL? This is trivial to do without SQL whether you want a macro or a macro variable.

[adityaa9z]

Hey there. Yes. I would like to hold this date 01JAN2017.

SQL because I was asked to do it using SQL. 😞 I wont mind other ways too.

[PaigeMiller]

You didn't answer my question about whether or not you want a macro or a macro variable. "Yes" does not answer the question.

However, if you want a macro variable, try this:

%let date=01jan2017;

Why bother with SQL?

[Tom]

The form for a date literal is quoted date in DATE format with the letter D after it.

To create a macro variable use the %LET macro statement.

%let myvar='01JAN2017'd;

If you want to calculate the first day of the current year use the INTNX() function.

%let myvar="%sysfunc(intnx(year,"&sysdate9"d,b),date9)"d;

[PaigeMiller]

@Tom, that's the answer if the question asks for a SAS date value, which as far as I can read, was not requested. The question asks for the text 01JAN2017 to be included in a macro (or macro variable, it's not really clear).

[adityaa9z]

My bad. Needed a macro. 🙂

[mkeintz · Accepted solution]

Or you can even avoid using the unsatisfying %sysfunc(intnx(...)) syntax by treating &sysdate9 as the text that it is: 

    %let mydate=01jan%substr(&sysdate9,6,4);

BTW, I would not include the quotes in macrovar value.  Instead, when the date value is needed, I'd use "&mydate"d.  It makes the type of value in the macrovar more apparent in the sas code.

And if one absolutely must use proc sql, you apparently have to have a "from" dataset even though no dataset content is being used (perhaps that's the point of being required to use SQL?).  I couldn't find a way to avoid the FROM clause below:

proc sql noprint;

  select cats('01jan',substr("&sysdate9",6,4)) into :mymac from sashelp.class ;

quit;

[adityaa9z]

Quite late to reply. But thanks mate. This is what i was looking for. 🙂