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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
ballardw
Super User

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;

DSF
Calcite | Level 5 DSF
Calcite | Level 5

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.

RichardinOz
Quartz | Level 8

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

DSF
Calcite | Level 5 DSF
Calcite | Level 5

Cool, thanks Richard!

Tom
Super User Tom
Super User

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

DSF
Calcite | Level 5 DSF
Calcite | Level 5

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!

Tom
Super User Tom
Super User

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

DSF
Calcite | Level 5 DSF
Calcite | Level 5

Awesome, thanks Tom!!

Patrick
Opal | Level 21

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

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
  • 9 replies
  • 6276 views
  • 6 likes
  • 5 in conversation