Help using a date in single quotes using sysdate

Accepted Solution Solved
Reply
Occasional Contributor DSF
Occasional Contributor
Posts: 7
Accepted Solution

Help using a date in single quotes using sysdate

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
Solution
‎06-03-2013 09:17 PM
Super User
Super User
Posts: 6,502

Re: Help using a date in single quotes using sysdate

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.)'"));

View solution in original post


All Replies
Super User
Posts: 10,516

Re: Help using a date in single quotes using sysdate

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;

Occasional Contributor DSF
Occasional Contributor
Posts: 7

Re: Help using a date in single quotes using sysdate

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.

Super Contributor
Posts: 644

Re: Help using a date in single quotes using sysdate

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

Attachment
Occasional Contributor DSF
Occasional Contributor
Posts: 7

Re: Help using a date in single quotes using sysdate

Cool, thanks Richard!

Super User
Super User
Posts: 6,502

Re: Help using a date in single quotes using sysdate

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'")) ;

Occasional Contributor DSF
Occasional Contributor
Posts: 7

Re: Help using a date in single quotes using sysdate

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!

Solution
‎06-03-2013 09:17 PM
Super User
Super User
Posts: 6,502

Re: Help using a date in single quotes using sysdate

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.)'"));

Occasional Contributor DSF
Occasional Contributor
Posts: 7

Re: Help using a date in single quotes using sysdate

Awesome, thanks Tom!!

Respected Advisor
Posts: 3,896

Re: Help using a date in single quotes using sysdate

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));

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 2236 views
  • 6 likes
  • 5 in conversation