- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Any help would be much appreciated!
I am trying to automate a report that runs monthly, with a one month 'lag' and creates a variable in where the values are in single quotes.
So, for example, if I run the report today, it creates a variable that reads '01MAY2013' single quotes included.
I am able to create the macro variable that creates the variable by creating this:
%let report_month =%sysfunc(intx(month.1,"&sysdate"d,-1),MonYY7.);
i just can't figure it out how to get the date in quotes such that it in the format '01MAY2013' d.
Thanks in advance!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Right, so just do that, you already had the function in your original post, just the wrong output format.
%let report_month =%sysfunc(dequote("'%sysfunc(intnx(month.1,"&sysdate9"d,-1),date9.)'"));
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
How are you using the Report_month variable? If you leave off the ,Monyy7. in the %sysfunc call then you have the actual value that '01May2013'd represents and don't need to mess with any quotes at all. You would be able to for instance:
If testdate le &Report_month;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
thanks ballardw,
Not sure that works though. I should have mentioned that I need the variable to be the first day of the previous month. so, if i were to run it july 4, 2013 for example, it would read '01JUN2013'. I have to run it this way to conform to the rest of the code in the report unfortunately.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you are needing to wrap multiple dates in single quotes, you might prefer to define specific macro functions to achieve this aim. A while ago I wrote a paper on how to do this. Macro functions can simplify your code but be sure to compile them or include them in your autolib option.
Richard
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Cool, thanks Richard!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you are using in just with SAS (as a date literal for instance) then you can just use double quotes instead of single quotes. "13MAY2013"d works just as well as '13MAY2013'd.
%let report_month ="%sysfunc(intx(month.1,"&sysdate9"d,-1),date9.)"d ;
If you really need to add single quotes around a macro variable value there are a few ways.
You could use macro quoting to get the single quotes without blocking the macro expansion.
%put %str(%')&sysdate9%str(%');
You could also use the DEQUOTE() function and fact that you can embed single quotes within double quotes and still get macro expansion.
%put %sysfunc(dequote("'&sysdate9'")) ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Tom,
I actually need not just the sysdate, but the first day of the previous month relative to sysdate, in single quotes to conform with how the code is set to run now. so for example, if i were to run it july 4, 2013 for example, it would read '01JUN2013'.
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Right, so just do that, you already had the function in your original post, just the wrong output format.
%let report_month =%sysfunc(dequote("'%sysfunc(intnx(month.1,"&sysdate9"d,-1),date9.)'"));
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Awesome, thanks Tom!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Tom has already given you the solution for creating a macro variable value in the form you asked for which was: '01MAY2013' d
I assume you then want to use the macro variable in your code in a way like: where date=&report_month
If so then you could also populate the macro variable with a SAS date value instead of an expression which then resolves into a SAS date value.
Following this thought something like below would do the job as well:
%let report_month =%sysfunc(intnx(month,"&sysdate9"d,-1,b));