topic Re: Create a macro to hold 1jan2017 using sql in SAS Programming

Create a macro to hold 1jan2017 using sql

adityaa9z — Mon, 22 May 2017 19:48:41 GMT

Todays date is 5/23/2017.

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

Re: Create a macro to hold 1jan2017 using sql

PaigeMiller — Mon, 22 May 2017 19:50:34 GMT

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.

Re: Create a macro to hold 1jan2017 using sql

adityaa9z — Mon, 22 May 2017 19:55:33 GMT

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.

Re: Create a macro to hold 1jan2017 using sql

PaigeMiller — Mon, 22 May 2017 19:59:38 GMT

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?

Re: Create a macro to hold 1jan2017 using sql

Tom — Mon, 22 May 2017 19:59:17 GMT

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;

Re: Create a macro to hold 1jan2017 using sql

PaigeMiller — Mon, 22 May 2017 20:02:09 GMT

@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).

Re: Create a macro to hold 1jan2017 using sql

adityaa9z — Mon, 22 May 2017 20:12:39 GMT

My bad. Needed a macro. 🙂

Re: Create a macro to hold 1jan2017 using sql

mkeintz — Mon, 22 May 2017 20:54:33 GMT

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;

Re: Create a macro to hold 1jan2017 using sql

adityaa9z — Thu, 25 May 2017 19:06:02 GMT

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