BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
adityaa9z
Obsidian | Level 7

Todays date is 5/23/2017.

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
adityaa9z
Obsidian | Level 7
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
Diamond | Level 26

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?

--
Paige Miller
Tom
Super User Tom
Super User

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
Diamond | Level 26

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

--
Paige Miller
adityaa9z
Obsidian | Level 7
My bad. Needed a macro. 🙂
mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
adityaa9z
Obsidian | Level 7
Quite late to reply. But thanks mate. This is what i was looking for. 🙂

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
  • 8 replies
  • 870 views
  • 0 likes
  • 4 in conversation