topic Global macro variable in SAS Procedures

Global macro variable

willy0625 — Tue, 20 Sep 2011 01:14:22 GMT

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

Global macro variable

Reeza — Tue, 20 Sep 2011 02:01:18 GMT

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.

Global macro variable

JohnsonIp — Tue, 20 Sep 2011 03:06:37 GMT

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;

Global macro variable

MichelleHomes — Tue, 20 Sep 2011 05:32:07 GMT

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;

Global macro variable

Tom — Tue, 20 Sep 2011 13:58:08 GMT

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;

Global macro variable

art297 — Tue, 20 Sep 2011 14:54:32 GMT

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

Global macro variable

willy0625 — Tue, 20 Sep 2011 22:50:11 GMT

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.

Global macro variable

art297 — Tue, 20 Sep 2011 22:58:02 GMT

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

Global macro variable

Ksharp — Wed, 21 Sep 2011 06:39:06 GMT

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

Global macro variable

Howles — Wed, 21 Sep 2011 21:47:45 GMT

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

Global macro variable

Ksharp — Thu, 22 Sep 2011 03:28:30 GMT

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

Global macro variable

Tom — Thu, 22 Sep 2011 11:02:00 GMT

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;

Global macro variable

Ksharp — Fri, 23 Sep 2011 02:23:43 GMT

Tom.

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

Global macro variable

Tom — Fri, 23 Sep 2011 13:18:24 GMT

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.

Global macro variable

Ksharp — Mon, 26 Sep 2011 03:33:31 GMT

Thanks . Tom. Educated it.