topic Re: Conditional Macros using &sysdate in SAS Programming

Conditional Macros using &sysdate

jibra — Thu, 04 Feb 2016 15:09:08 GMT

I've been able to create a few macros to format the date like I want and to strip month and year out of &ysdate. For example:

%let refyear=%sysfunc(year("&sysdate"d)); results in "2016"

%let fulldate= %sysfunc(date(),worddate18.); results in "February 4, 2016"

%let refmofirn=%eval(%sysfunc(month("&sysdate"d))-1); results in "1"

My problem is that when I try to take it further using conditional statements I hit a wall. What I want to code is:

If the current month is January, then I want the refyear to be the current year-1, and if the current month is anythig else, I want the refyear to be the current year. So clipping some stuff online, I did the following to no avail:

%macro refyear;

%if %eval(%sysfunc(month("&sysdate"d))-1)= 1 %then %eval(%sysfunc(year("&sysdate"d))-1);

%else %sysfunc(year("&sysdate"d));

%mend refyear;

I'm pretty sure I'm missing something important. I've seen this with do loops that I can't understand quite yet.

I'd like to be able to strim out a MMM from Sysdate based on the following conditions:

if the curent month is January, then let refmofir=Dec and refmosec=Nov

if the current month is February, then refmofir=Jan and refmosec=Dec

if the current month is March, then let refmofir=Feb refmosec=Jan

and so on. I'm not sure if all of what I want can be done in one macro statement.

Re: Conditional Macros using &sysdate

Quentin — Thu, 04 Feb 2016 15:26:49 GMT

I'm not quite following your macro. If the goal is to have it return the previous year when it's January, and the current year in any other month, then you dont need to subtract 1 from the month. So this works:

%macro refyear(date=);
  %if %sysfunc(month("&date"d))= 1 %then %eval(%sysfunc(year("&date"d))-1);
  %else %sysfunc(year("&date"d));
%mend refyear;

50   %put %refyear(date=05Jan2016);
2015
51   %put %refyear(date=01Feb2016);
2016
52   %put %refyear(date=&sysdate);
2016
53   %put %refyear(date=01Mar2016);
2016

That said, you probably don't need a macro for this at all. The you can use the INTNX function to find the year of the previous month instead. Like:

55   %put %sysfunc(intnx(month,"05Jan2016"d,-1), year.);
2015
56   %put %sysfunc(intnx(month,"01Feb2016"d,-1), year.);
2016
57   %put %sysfunc(intnx(month,"&sysdate"d,-1), year.);
2016
58   %put %sysfunc(intnx(month,"01Mar2016"d,-1), year.);
2016

Re: Conditional Macros using &sysdate

RW9 — Thu, 04 Feb 2016 15:29:30 GMT

Use a datastep. Base SAS is for manipulating and processing data, Macro is for creating text.

data _null_;
  my_date=put(today(),date9.;
  call symput('WANT_DATE',my_date);
run;

To be honest, I would keep all parameters in a dataset however, unless there is a specific reason to put to macro variable. I mean if you want to manipulate the data, you could either get a ton of messy obfuscated macro code, or just do it in the dataset and put it out again.

Re: Conditional Macros using &sysdate

jibra — Thu, 04 Feb 2016 16:37:17 GMT

Sorry, Quentin. After rereading my post, I can see how it can be confusing. I only subtract 1 in times when the job is run in February and includes data up to January. The reason I'm creating macros for a refyear is to call on them later in a report when I need to highlight certain cells. So my macros are:

%let refyear=2015;

%let refmofir=Dec; *Current month (first closing);

%let refmosec=Nov; *Previous month (2nd closing);

%let refmofirn=12; *Current month number (first closing);

I had read about the possibility of automating even these so that any user can just open up the program and run it without having to reenter data. I call on the macros to highlight certain cells for the current and previous months by a compute statement:

compute emp;

if &refmofirn = 1 then do;

if year = &refyear.-1 then call define("_c13_","style","style={background=gold}"); *dec;

if year = &refyear. then call define("_c2_","style","style={background=gold}"); *jan;

end;

else do;

if (year=&refyear. )then do;

call define("_c%eval(&refmofirn+1)_","style","style={background=gold}"); *cur month;

call define("_c%eval(&refmofirn)_","style","style={background=gold}"); *prev month;

end;

endcomp;

This way, in January, both December of refyear-1 and January of refyear are highlighted. In all other cases, the consecutive months of refyear are highlighted. See attachments. If I follow your macro, will i have to enter the put statments directly into my compute statement? I've been able to accomplish quite a bit based on the SAS community's responses as well as Tech Support's input, but I'm not sure how to integrate all I learn without major setbacks or glitches.

Re: Conditional Macros using &sysdate

Quentin — Thu, 04 Feb 2016 18:06:23 GMT

I would not use the macro, I think it will be easier without it. I think your IF statement could be like:

*if year equals the year of the previous month;
if year = %sysfunc(intnx(month,"&sysdate"d,-1),year.) then call define("_c13_","style","style={background=gold}");

Re: Conditional Macros using &sysdate

jibra — Thu, 04 Feb 2016 21:16:23 GMT

Quentin, so I'm thinking this might work, but I'm screwing up applying it somehow. This is my version:

compute emp;

if %eval(%sysfunc(month("&sysdate"d))-1) = 1 then do;

if year = %sysfunc(intnx(month,"&sysdate"d,-1),year.) then call define("_c13_","style","style={background=gold}"); *dec;

if year = %sysfunc(year("&sysdate"d)) then call define("_c2_","style","style={background=gold}"); *jan;

end;

else do;

if (year=%sysfunc(year("&sysdate"d)) )then do;

call define("_c%eval(%sysfunc(month("&sysdate"d))+1)_","style","style={background=gold}"); *cur month;

call define("_c%eval(%sysfunc(month("&sysdate"d)))_","style","style={background=gold}"); *prev month;

end;

endcomp;

So I guess I'm not sure how to avoid the macro. How can I say this properly in code:

if the (today's month - 1) is 1, then highlight gold in _c13_ of row (today's year-1) and highlight gold in _c2_ of row (today's year).

conversely if (today's month - 1) ne 1, then highlight gold in _column# equal to today's month+1_ and highlight gold in _column# equal to today's month_.

Re: Conditional Macros using &sysdate

Quentin — Fri, 05 Feb 2016 02:02:17 GMT

I don't have a quick answer, but here are my thoughts. I think it's likely your question is not really a macro question, it's a PROC REPORT question.

The tricky thing about the macro language is that you use it to generate SAS code, instead of typing SAS code yourself. So when something goes wrong with a macro, you need to ask two questions:

1. Is my macro code generating the SAS code I want?

2. Is the SAS code I want correct?

When starting with macro, sometimes it helps to start by writing SAS code with no macro stuff, and get that working. There is plenty of work to do in debugging SAS code, without worrying about tthe macro language.

It looks to me like your current version would generate the following SAS code (if run in Feburary of 2016). I've resolved all the macro references by hand, so apologies if I've made a mistake:

compute emp;

   if 1 = 1 then do;
      if year = 2016 then call define("_c13_","style","style={background=gold}"); *dec;
      if year = 2016 then call define("_c2_","style","style={background=gold}"); *jan;
   end;

   else do;
      if (year=2016) then do;
         call define("_c3_","style","style={background=gold}"); *cur month;
         call define("_c2_","style","style={background=gold}"); *prev month;
      end;
   end;

 endcomp;

To my eye, that generated code looks consistent with your description of what you want. But I would try running that code, and see if it gives you the results you want. There is plenty of trickery involved in debugging COMPUTE blocks (it looks like data step language, but there are complexities).

Once you've got working PROC REPORT code, then try adding the macro language to make it more dynamic.

Hope that helps.

Re: Conditional Macros using &sysdate

RW9 — Fri, 05 Feb 2016 10:41:54 GMT

Have you condsidered setting your data up before the report procedure? So in your data apply some flags:

data report;

set your_data;

length flag1 flag2 $1;

if <logic> then flag1=1;

if <logic> then flag2=1;

run;

Then in your report, break on each of the flags, and use style formatting on the break groups? Its easier to processing the data in a datastep, then use the report to just print.

Re: Conditional Macros using &sysdate

jibra — Fri, 05 Feb 2016 15:36:03 GMT

Quentin, Thank you very much for this discussion. This is actually what the logic should be:

compute emp;

   if month = 1 then do;
      if year = 2015 then call define("_c13_","style","style={background=gold}"); *dec;
      if year = 2016 then call define("_c2_","style","style={background=gold}"); *jan;
   end;

   else do;
      if (year=2016) then do;
         call define("_c3_","style","style={background=gold}"); *cur month;
         call define("_c2_","style","style={background=gold}"); *prev month;
      end;
   end;
 endcomp;

Like I said, my proc report runs fine if I set two macros at the beginning fo the program: %let refyear=2016; %let refmofirn=1;

And I use the following compute command in my proc report:

compute emp;
	if &refmofirn = 1 then do;
		if year = &refyear.-1 then call define("_c13_","style","style={background=gold posttext='(P)'}"); *dec;
		if year = &refyear. then call define("_c2_","style","style={background=gold posttext='(P)'}"); *jan;
	end;
	else do;
		if (year=&refyear. )then do;
        	call define("_c%eval(&refmofirn+1)_","style","style={background=gold posttext='(P)'}"); *cur month;
        	call define("_c%eval(&refmofirn)_","style","style={background=gold posttext='(P)'}");  *prev month;
		end;
    end;
endcomp;

I am only trying to remove the need for my users to update the two macros each month. I thought the following logic would do it:

compute emp;
	if  %eval(%sysfunc(month("&sysdate"d))-1) = 1 then do;
		if year = %eval(%sysfunc(year("&sysdate"d)-1)) then call define("_c13_","style","style={background=gold posttext='(P)'}"); *dec;
		if year = %sysfunc(year("&sysdate"d)) then call define("_c2_","style","style={background=gold posttext='(P)'}"); *jan;
	end;
	else do;
		if (year=%sysfunc(year("&sysdate"d) ))then do;
        	call define("_c%eval(%sysfunc(month('&sysdate'd))+1)_","style","style={background=gold posttext='(P)'}"); *cur month;
        	call define("_c%sysfunc(month('&sysdate'd))_","style","style={background=gold posttext='(P)'}");  *prev month;
		end;
    end;
endcomp;

I'm getting no output with the last revision and I'm only getting an "ERROR: Expected close parenthesis after macro function invocation not found" in the log.

As far as using the data step that good members of the community have suggested, I've never really done anythign like this in order to highlight something in a proc report. I have created data sets that I've applied in annotation tables, so I'm not exactly sure how to build that. would I merely flag something in my data set and then add a compute command with a where flag=... condition in proc report?

Re: Conditional Macros using &sysdate

RW9 — Fri, 05 Feb 2016 15:53:45 GMT

As for the last paragraph, yes, that's what I was suggesting. This is actually all proc report is doing as well, if you add out= to your proc report and see the dataset that it uses, there will be flags for certain data elements.

Re: Conditional Macros using &sysdate

Quentin — Fri, 05 Feb 2016 16:20:11 GMT

Good that you are now getting an error message. That helps.

The parenthese are wrong in:

if year = %eval(%sysfunc(year("&sysdate"d)-1))

You want:

if year = %eval(%sysfunc(year("&sysdate"d))-1)

The easiest way to test such stuff is with %PUT statements. These simply write text to the log. Here is my log from running the wrong version and right version:

52   %put WRONG: %eval(%sysfunc(year("&sysdate"d)-1));
ERROR: Expected close parenthesis after macro function invocation not found.
WRONG: 1)
53   %put RIGHT: %eval(%sysfunc(year("&sysdate"d))-1);
RIGHT: 2015

Re: Conditional Macros using &sysdate

jibra — Fri, 05 Feb 2016 17:42:42 GMT

Thank you Quentin that solved the issue.

Re: Conditional Macros using &sysdate

jibra — Fri, 05 Feb 2016 17:48:06 GMT

Quentin that solves my problems. Thank you very much for your input. I can avoid putting macros at the beginning of the program by coding the sysdate macro directly into the proc report. I used the code below and it worked quite well:

compute variable;
	if  %eval(%sysfunc(month("&sysdate"d))-1)= 1 then do;
		if year = %eval(%sysfunc(year("&sysdate"d))-1) then call define("_c13_","style","style={background=gold posttext='(P)'}"); *dec;
		if year = %sysfunc(year("&sysdate"d)) then call define("_c2_","style","style={background=gold posttext='(P)'}"); *jan;
	end;
	else do;
		if (year=%sysfunc(year("&sysdate"d)))then do;
        	call define("_c%eval(%sysfunc(month('&sysdate'd))-1)_","style","style={background=gold posttext='(P)'}"); *cur month;
        	call define("_c%sysfunc(month('&sysdate'd))_","style","style={background=gold posttext='(P)'}");  *prev month;
		end;
    end;
endcomp;