Help using Base SAS procedures

Global macro variable

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Global macro variable

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

Attachment

Accepted Solutions
Solution
‎09-20-2011 01:32 AM
Trusted Advisor
Posts: 1,253

Global macro variable

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;

View solution in original post


All Replies
Super User
Posts: 17,912

Global macro variable

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.


Occasional Contributor
Posts: 8

Global macro variable

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;

Solution
‎09-20-2011 01:32 AM
Trusted Advisor
Posts: 1,253

Global macro variable

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;

Super User
Super User
Posts: 6,502

Global macro variable

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;

PROC Star
Posts: 7,364

Global macro variable

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

Contributor
Posts: 27

Global macro variable

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.

PROC Star
Posts: 7,364

Global macro variable

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

Super User
Posts: 9,687

Global macro variable

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

Regular Contributor
Posts: 184

Global macro variable

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

Super User
Posts: 9,687

Global macro variable

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

Super User
Super User
Posts: 6,502

Global macro variable

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;

Super User
Posts: 9,687

Global macro variable

Tom.

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

Super User
Super User
Posts: 6,502

Global macro variable

  • 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.
Super User
Posts: 9,687

Global macro variable

Thanks . Tom. Educated it.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 312 views
  • 9 likes
  • 8 in conversation