DATA Step, Macro, Functions and more

Create a macro to hold 1jan2017 using sql

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Create a macro to hold 1jan2017 using sql

Todays date is 5/23/2017.

 

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

 

 


Accepted Solutions
Solution
‎05-25-2017 03:03 PM
Valued Guide
Posts: 797

Re: Create a macro to hold 1jan2017 using sql

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;

 

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,617

Re: Create a macro to hold 1jan2017 using sql

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.

Contributor
Posts: 27

Re: Create a macro to hold 1jan2017 using sql

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

SQL because I was asked to do it using SQL. Smiley Sad I wont mind other ways too.
Trusted Advisor
Posts: 1,617

Re: Create a macro to hold 1jan2017 using sql

[ Edited ]

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?

Super User
Super User
Posts: 6,502

Re: Create a macro to hold 1jan2017 using sql

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;
Trusted Advisor
Posts: 1,617

Re: Create a macro to hold 1jan2017 using sql

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

Contributor
Posts: 27

Re: Create a macro to hold 1jan2017 using sql

My bad. Needed a macro. Smiley Happy
Solution
‎05-25-2017 03:03 PM
Valued Guide
Posts: 797

Re: Create a macro to hold 1jan2017 using sql

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;

 

 

Contributor
Posts: 27

Re: Create a macro to hold 1jan2017 using sql

Quite late to reply. But thanks mate. This is what i was looking for. Smiley Happy
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 152 views
  • 0 likes
  • 4 in conversation