topic Help using a date in single quotes using sysdate in SAS Programming

Help using a date in single quotes using sysdate

DSF — Mon, 03 Jun 2013 14:45:45 GMT

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!

Re: Help using a date in single quotes using sysdate

ballardw — Mon, 03 Jun 2013 15:10:59 GMT

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;

Re: Help using a date in single quotes using sysdate

Tom — Mon, 03 Jun 2013 15:11:30 GMT

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

Re: Help using a date in single quotes using sysdate

DSF — Tue, 04 Jun 2013 00:46:46 GMT

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.

Re: Help using a date in single quotes using sysdate

DSF — Tue, 04 Jun 2013 00:48:49 GMT

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!

Re: Help using a date in single quotes using sysdate

Tom — Tue, 04 Jun 2013 01:17:28 GMT

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

Re: Help using a date in single quotes using sysdate

RichardinOz — Tue, 04 Jun 2013 09:56:21 GMT

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

Re: Help using a date in single quotes using sysdate

Patrick — Tue, 04 Jun 2013 10:43:46 GMT

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

Re: Help using a date in single quotes using sysdate

DSF — Tue, 04 Jun 2013 16:25:46 GMT

Awesome, thanks Tom!!

Re: Help using a date in single quotes using sysdate

DSF — Tue, 04 Jun 2013 16:27:32 GMT

Cool, thanks Richard!