Global macro variable

[willy0625]

Hello SAS users.

I have a question about global macro variable.

I would like to obtain a global macro called N which needs to be the number of quarters there are between today and the minimum of enquiry_date variable from the sample dataset.

Note, I want to use N as an upper bound of the DO loop of several macros so I do not have to count N manually everytime I run my code. The following is what I do:

*Today's Date;

%LET dataend = 19sep2011;

*Extracting the earliest enquiry date and write the date into a macro variable called datebig from the sample dataset;

PROC SQL;

      SELECT MIN(enquiry_date) INTO: datebig

      FROM sample;

QUIT;

*Calculate N;

%MACRO C;

      %LET N=ceil((&dataend-&datebig)/(365.25/4));

      %PUT &N;

%MEND C;

%C;

This is not running properly. From my log, it seems that SAS is not recognising &datebig as a numeric variable..

Any ideas?

Thanks

[Reeza]

Assuming enquiry_date is actually a date field, I think SAS may not be recognizing dataend as a date variable.

try

%LET dataend = '19sep2011'd;

and rerun.

[JohnsonIp]

Hi,

I think the program need to define the macro variable as date and  call %sysfunc to conduct the calculation.  I modify the program with bold characters.

Hope it help.

Johnson

%LET dataend = '19sep2011'd;

*Extracting the earliest enquiry date and write the date into a macro variable called datebig from the sample dataset;

PROC SQL;

      SELECT MIN(enquiry_date) INTO: datebig

      FROM sample;

QUIT;

*Calculate N;

%MACRO C;

      %LET N=%sysfunc(ceil((&dataend-&datebig)/(365.25/4)));

      %PUT &N = ;

%MEND C;

%C;

[MichelleHomes · Accepted solution]

Hi There,

There are a number of ways you can do this. One way is to treat the macro variable as text (as that is what it is) and then in your SAS code use it as a SAS date constant. You need to have double quotes around the dataend macro variable so that it is resolved by the macro compiler. See your original code below with my changes in bold...

However a better way would be to use the SAS intck function where you can specify "qtr" as the interval rather than the calculation you have specified as this way leap years are calculated more accurately.

e.g. %LET N=%sysfunc(intck("qtr",&datebig,"&dataend"d));

Hope this helps.

Cheers,

Michelle

------------

*Today's Date;

%LET dataend = 19sep2011;

*Extracting the earliest enquiry date and write the date into a macro variable called datebig from the sample dataset;

PROC SQL;

      SELECT MIN(enquiry_date) INTO: datebig

      FROM sample;

QUIT;

*Calculate N;

%MACRO C;

      %LET N=ceil(("&dataend"d-&datebig)/(365.25/4));

      %PUT &N;

%MEND C;

%C;

[Tom]

Hopefully you have your answer with the INTCK function.

In fact you should be able to get the number you want just from the SQL query.

PROC SQL;

      SELECT inctck('QTR',MIN(enquiry_date),today()) INTO :N

      FROM sample;

QUIT;

[art297]

You have already received an answer regarding how to correct your macro to accomplish what you are trying to do but, since some have suggested the intck function, I have a couple of questions.

The intck function, by itself, will NOT give you the same answer as your current method.  Are you trying to identify number of quarters based on jan1-mar31, apr1-jun30, etc., OR is Sep19 the start or end of a quarter?

And, do you just need the calculation for the one range, or are you going to be calculating it for all of your records?

If you are defining quarters based on the Sep19-Dec18, Dec19-Mar18, etc. definition, and need to do it for all of your dates, I'd recommend using the intck function but combining it with the method suggested in the following thread: http://communities.sas.com/message/104501#104501

[willy0625]

Hi art297.

I was wondering about this actually.

What I mean by quarters is based on the calendar - jan1-mar31(1st quarter), apr1-jun30(2nd quarter), ... etc.

So if we start from September the 19th, this falls in the 3rd quarter of 2011.

Also, I would be using this method for a much bigger dataset with abou 14,000,000 obervations and hence 14,000,000 enquiry dates.

Thank you Michelle and Tom for the intck function and also art for reminding me of the quarter issue.

[art297]

Then, definitely, the intck function is what you need.  Your corrected original macro would not have provided the correct answer.

[Ksharp]

Another implying problem is that macro variable  &datebig is local not golbal,which 's lifetime is only for proc sql.

SO you need to adjust it to make it global.

Such as outside before proc sql.

%global datebig;

proc sql;

.....................

Ksharp

[Howles]

I don't think so. If the PROC SQL statement which creates and populates the macro variable is in open code (not macro-generated), the macro variable is global by default.

proc sql noprint ;

select max(age) into : datebig from sashelp.class ;

quit ;

%put _user_ ;

%symdel datebig ;

Of course if the SQL code IS macro-generated, the default scope takes effect and you do not get a global.

%macro amacro ;

proc sql noprint ;

select max(age) into : datebig from sashelp.class ;

quit ;

%mend ;

%amacro

%put _user_ ;

To force a global, the %GLOBAL statement has to preceed the SQL statement, but it can be within the boundaries of the PROC SQL step.

%macro amacro ;

proc sql noprint ;

%global datebig ;

select max(age) into : datebig from sashelp.class ;

quit ;

%mend ;

%amacro

%put _user_ ;

%symdel datebig ;

Ksharp wrote:
Another implying problem is that macro variable  &datebig is local not golbal,which 's lifetime is only for proc sql.
SO you need to adjust it to make it global.
Such as outside before proc sql.
%global datebig;
proc sql;
.....................
Ksharp

[Ksharp]

Howles.

Yes. I tested it. It is global in log.

But Sometimes, When code sql without %global, SAS looks like can not  solve it generated by sql.

It is so weird. So I usually declare %global before sql to make sure it is golbal.

I don't know why it will happen ? Maybe My sas 's version  is too low.

Ksharp

[Tom]

What you experienced is most likely because the query did not match any observations.  If you use SQL to create a macro variable and the query does not match any records then SQL does not create the macro variable.  I usually just use a %LET before the query to set the value that I want if the query does not macth any observations.  Using %global has other side effects, the worst being errors when the requested macro variable name already exists in a currently active local scope.

proc sql noprint ;

%let datebig=;

  select max(age) into : datebig from sashelp.class ;

quit;

[Ksharp]

Tom.

Could you explain what other side effects %golbal will generate?

[Tom]

• If the macro variable already exists the %GLOBAL does not change its value.  In this piece of code the intent was to initialize the variable so that will have a value even if SQL did not find any observations.  So the side effect would be that the pre-existing value persists even after this block of code runs.
• The %LET method does not care whether the variable is local (to the executing macro) or external (global or local to a surrounding macro scope) and so is more flexible.
• I do not like about overuse of %GLOBAL is that it clutters the global macro space.
• I write a lot of utility macros and I am careful to make sure to define the inputs and outputs to avoid unintended interactions.  Within a macro I  will frequently use %LOCAL to define (and initialize to empty) a variable.

[Ksharp]

Thanks . Tom. Educated it.