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

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

1 ACCEPTED SOLUTION

Accepted Solutions
MichelleHomes
Meteorite | Level 14

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;

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com

View solution in original post

14 REPLIES 14
Reeza
Super User

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
Calcite | Level 5

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
Meteorite | Level 14

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;

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Tom
Super User Tom
Super User

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
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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

Ksharp
Super User

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
Quartz | Level 8

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
Super User

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
Super User Tom
Super User

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
Super User

Tom.

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

Tom
Super User Tom
Super User
  • 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
Super User

Thanks . Tom. Educated it.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 14 replies
  • 2040 views
  • 9 likes
  • 8 in conversation